List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:October 7 2011 4:20pm
Subject:Re: MySQL Indexes
View as plain text  
but could this not be called a bug?

Am 07.10.2011 18:08, schrieb Michael Dykman:
> When a query selects on field_a and field_b, that index can be used.  If
> querying on field_a alone, the index again is useful.  Query on field_b
> alone however, that first index is of no use to you.
> 
> On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps <bphelps@stripped> wrote:
> 
>> 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>
>>>>>>
>>>>> 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@**
>>>>> googlemail.com <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@**gmail.com
> <mdykman@stripped>
>>>>>
>>>>>  May the Source be with you.
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:   
> http://lists.mysql.com/mysql?**unsub=mdykman@ style="color:#666">stripped<http://lists.mysql.com/mysql?unsub=1
>>
>>
> 
> 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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