From: Neil Tompkins Date: October 6 2011 6:35pm Subject: Re: MySQL Indexes List-Archive: http://lists.mysql.com/mysql/225947 Message-Id: MIME-Version: 1.0 (iPhone Mail 8L1) Content-Type: multipart/alternative; boundary=Apple-Mail-2-316526825 Content-Transfer-Encoding: 7bit --Apple-Mail-2-316526825 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Maybe that was a bad example. If the query was name =3D 'Red' what index sh= ould I create ? Should I create a index of all columns used in each query or have a index on= individual column ? On 6 Oct 2011, at 17:28, Michael Dykman wrote: > For the first query, the obvious index on score will give you optimal resu= lts. >=20 > The second query is founded on this phrase: "Like '%Red%' " and no index w= ill help you there. This is an anti-pattern, I am afraid. The only way you= r database can satisfy that expression is to test each and every record in t= he that database (the test itself being expensive as infix finding is iterat= ive). Perhaps you should consider this approach instead: > http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html >=20 > On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil wrote: > Hi, >=20 > Can anyone help and offer some advice with regards MySQL indexes. Basical= ly > we have a number of different tables all of which have the obviously prima= ry > keys. We then have some queries using JOIN statements that run slowly tha= n > we wanted. How many indexes are recommended per table ? For example shou= ld > I have a index on all fields that will be used in a WHERE statement ? > Should the indexes be created with multiple fields ? A example of two > basic queries >=20 > SELECT auto_id, name, score > FROM test_table > WHERE score > 10 > ORDER BY score DESC >=20 >=20 > SELECT auto_id, name, score > FROM test_table > WHERE score > 10 > AND name Like '%Red%' > ORDER BY score DESC >=20 > How many indexes should be created for these two queries ? >=20 > Thanks, > Neil >=20 >=20 >=20 > --=20 > - michael dykman > - mdykman@stripped >=20 > May the Source be with you. --Apple-Mail-2-316526825--