FW: MySQL Indexes

>-----Original Message-----
>From: Reindl Harald [mailto:h.reindl@thelounge.net]
>Sent: Friday, October 07, 2011 12:21 PM
>To: mysql@lists.mysql.com
>Subject: Re: MySQL Indexes
>
>but could this not be called a bug?
>
[JS] No.

Think of two telephone books: one is sorted by first name, last name and the
other is sorted by last name, first name. (Those are like your two keys, f1/f2
and f2/f1.)

If you want to find someone by their first name, you use the first book. If
you want to find somebody by their last name, you use the second book.

If you want to find someone by their last name, the first book (key f1/f2) is
useless. If you want to find someone by their first name, the second book
(f2/f1) is useless.

Does that help explain it?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.giiresearch.com




>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 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
>>>>> **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 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:
>>>>>> >>>>>> natural-language.html >natural-language.html>
>>>>>>>
>>>>>> http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
>>>>>> natural-language.html >natural-language.html>
>>>>>>
>>>>>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil< >>>>>> googlemail.com >
>>>>>> neil.tompkins@googlemail.com> 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@**gmail.com
>>>>>>
>>>>>> 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@gmail.com >ysql?unsub=mdykman@gmail.com>
>>>
>>>
>>
>>
>
>--
>
>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





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: FW: MySQL Indexes

That cleared it up for me. Thanks!

On 10/07/2011 03:06 PM, Jerry Schwartz wrote:
>> -----Original Message-----
>> From: Reindl Harald [mailto:h.reindl@thelounge.net]
>> Sent: Friday, October 07, 2011 12:21 PM
>> To: mysql@lists.mysql.com
>> Subject: Re: MySQL Indexes
>>
>> but could this not be called a bug?
>>
> [JS] No.
>
> Think of two telephone books: one is sorted by first name, last name and the
> other is sorted by last name, first name. (Those are like your two keys, f1/f2
> and f2/f1.)
>
> If you want to find someone by their first name, you use the first book. If
> you want to find somebody by their last name, you use the second book.
>
> If you want to find someone by their last name, the first book (key f1/f2) is
> useless. If you want to find someone by their first name, the second book
> (f2/f1) is useless.
>
> Does that help explain it?
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
> E-mail: jerry@gii.co.jp
> Web site: www.giiresearch.com
>
>
>
>
>> 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 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
>>>>>> **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 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:
>>>>>>> >>>>>>> natural-language.html >> natural-language.html>
>>>>>>>>
>>>>>>> http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
>>>>>>> natural-language.html >> natural-language.html>
>>>>>>>
>>>>>>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil< >>>>>>> googlemail.com>
>>>>>>> neil.tompkins@googlemail.com> 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@**gmail.com
>>>>>>>
>>>>>>> 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@gmail.com >> ysql?unsub=mdykman@gmail.com>
>>>>
>>>>
>>>
>>>
>>
>> --
>>
>> 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
>
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org