From: Michael Dykman
Date: October 6 2011 4:28pm
Subject: Re: MySQL Indexes
List-Archive: http://lists.mysql.com/mysql/225941
Message-Id:
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=90e6ba21219bc3e1f204aea3d057
--90e6ba21219bc3e1f204aea3d057
Content-Type: text/plain; charset=ISO-8859-1
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 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.
--90e6ba21219bc3e1f204aea3d057--