Maybe that was a bad example. If the query was name = 'Red' what index should 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 <mdyk...@gmail.com> wrote: > For the first query, the obvious index on score will give you optimal results. > > The second query is founded on this phrase: "Like '%Red%' " and no index will > help you there. This is an anti-pattern, I am afraid. The only way your > database can satisfy that expression is to test each and every record in the > that database (the test itself being expensive as infix finding is > iterative). Perhaps you should consider this approach instead: > http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html > > On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil <neil.tompk...@googlemail.com> > wrote: > Hi, > > Can anyone help and offer some advice with regards MySQL indexes. Basically > we have a number of different tables all of which have the obviously primary > keys. We then have some queries using JOIN statements that run slowly than > we wanted. How many indexes are recommended per table ? For example should > 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 > > SELECT auto_id, name, score > FROM test_table > WHERE score > 10 > ORDER BY score DESC > > > SELECT auto_id, name, score > FROM test_table > WHERE score > 10 > AND name Like '%Red%' > ORDER BY score DESC > > How many indexes should be created for these two queries ? > > Thanks, > Neil > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you.
