List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:October 6 2011 6:35pm
Subject:Re: MySQL Indexes
View as plain text  
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.

Thread
MySQL IndexesTompkins Neil6 Oct
  • Re: MySQL IndexesMichael Dykman6 Oct
    • Re: MySQL IndexesNeil Tompkins6 Oct
      • Re: MySQL IndexesMichael Dykman7 Oct
        • Re: MySQL IndexesNuno Tavares7 Oct
          • Re: MySQL IndexesBrandon Phelps7 Oct
            • Re: MySQL IndexesMichael Dykman7 Oct
              • Re: MySQL IndexesReindl Harald7 Oct
                • Re: MySQL IndexesMichael Dykman7 Oct
                  • Re: MySQL IndexesNeil Tompkins7 Oct
                    • Re: MySQL IndexesMichael Dykman7 Oct
        • Re: MySQL IndexesTompkins Neil7 Oct
          • Re: MySQL IndexesMichael Dykman7 Oct
            • Re: MySQL IndexesNeil Tompkins7 Oct
              • Re: MySQL Indexesmos7 Oct