Re: MySQL Indexes

[Neil Tompkins]

Can you give more information as to why the second index would be of no use ?  

On 7 Oct 2011, at 18:24, Michael Dykman <mdykman@stripped> wrote:

> No, I don't think it can be called.  It is a direct consequence of the
> relational paradigm.  Any implementation of an RDBMS has the same
> characteristic.
> 
> - md
> 
> On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald <h.reindl@stripped>wrote:
> 
>> 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: [phone], m: [phone]
>> icq: 154546673, http://www.thelounge.net/
>> 
>> http://www.thelounge.net/signature.asc.what.htm
>> 
>> 
> 
> 
> -- 
> - michael dykman
> - mdykman@stripped
> 
> May the Source be with you.