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 <mdykman@stripped> 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.tompkins@stripped>
> 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
> - mdykman@stripped
>
> May the Source be with you.
