List:General Discussion« Previous MessageNext Message »
From:Brandon Phelps Date:October 7 2011 2:49pm
Subject:Re: MySQL Indexes
View as plain text  
This thread has sparked my interest. What is the difference between an index on (field_a,
field_b) and an index on (field_b, field_a)?

On 10/06/2011 07:43 PM, Nuno Tavares wrote:
> Neil, whenever you see multiple fields you'd like to index, you should
> consider, at least:
>
> * The frequency of each query;
> * The occurrences of the same field in multiple queries;
> * The cardinality of each field;
>
> There is a tool "Index Analyzer" that may give you some hints, and I
> think it's maatkit that has a tool to run a "query log" to find good
> candidates - I've seen it somewhere, I believe....
>
> Just remember that idx_a(field_a,field_b) is not the same, and is not
> considered for use, the same way as idx_b(field_b,field_a).
>
> -NT
>
>
> Em 07-10-2011 00:22, Michael Dykman escreveu:
>> Only one index at a time can be used per query, so neither strategy is
>> optimal.  You need at look at the queries you intend to run against the
>> system and construct indexes which support them.
>>
>>   - md
>>
>> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins
>> <neil.tompkins@stripped>wrote:
>>
>>> 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>
>>> 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>
>>> 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>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