MySQL Indexes

--20cf307c9f387ab97504aea292b8
Content-Type: text/plain; charset=ISO-8859-1

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

--20cf307c9f387ab97504aea292b8--

Re: MySQL Indexes

--90e6ba21219bc3e1f204aea3d057
Content-Type: text/plain; charset=ISO-8859-1

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-lang uage.html

On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil > 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.

--90e6ba21219bc3e1f204aea3d057--

Re: MySQL Indexes

--Apple-Mail-2-316526825
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

Maybe that was a bad example. If the query was name =3D 'Red' what index sh=
ould 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 resu=
lts.
>=20
> The second query is founded on this phrase: "Like '%Red%' " and no index w=
ill help you there. This is an anti-pattern, I am afraid. The only way you=
r database can satisfy that expression is to test each and every record in t=
he that database (the test itself being expensive as infix finding is iterat=
ive). Perhaps you should consider this approach instead:
> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.html
>=20
> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil om> wrote:
> Hi,
>=20
> Can anyone help and offer some advice with regards MySQL indexes. Basical=
ly
> we have a number of different tables all of which have the obviously prima=
ry
> keys. We then have some queries using JOIN statements that run slowly tha=
n
> we wanted. How many indexes are recommended per table ? For example shou=
ld
> 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
>=20
> SELECT auto_id, name, score
> FROM test_table
> WHERE score > 10
> ORDER BY score DESC
>=20
>=20
> SELECT auto_id, name, score
> FROM test_table
> WHERE score > 10
> AND name Like '%Red%'
> ORDER BY score DESC
>=20
> How many indexes should be created for these two queries ?
>=20
> Thanks,
> Neil
>=20
>=20
>=20
> --=20
> - michael dykman
> - mdykman@gmail.com
>=20
> May the Source be with you.

--Apple-Mail-2-316526825--

Re: MySQL Indexes

--00151773e42285118004aea99a3a
Content-Type: text/plain; charset=ISO-8859-1

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:
>
> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.html
>
> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil <
> 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.
>
>


--
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--00151773e42285118004aea99a3a--

Re: MySQL Indexes

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:
>>
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.html
>>
>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil <
>> 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=gcdmg-mysql-2@m.gmane.org

Re: MySQL Indexes

--20cf307c9f2cd17ce104aeb13285
Content-Type: text/plain; charset=ISO-8859-1

Is it normal practice for a heavily queried MYSQL tables to have a index
file bigger than the data file ?

On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman wrote:

> 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@googlemail.com> 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:
>>
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.html
>>
>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil <
>> 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.
>>
>>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.
>

--20cf307c9f2cd17ce104aeb13285--

Re: MySQL Indexes

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:
>>>
>>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.html
>>>
>>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil<
>>> 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=gcdmg-mysql-2@m.gmane.org

Re: MySQL Indexes

--90e6ba21219b10361304aeb7a75d
Content-Type: text/plain; charset=ISO-8859-1

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
>>>> >
>>>> http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
>>>> 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
>
>


--
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--90e6ba21219b10361304aeb7a75d--

Re: MySQL Indexes

--0015175dd79ca3fbba04aeb7af4b
Content-Type: text/plain; charset=ISO-8859-1

How heavily a given table is queried does not directly affect the index
size, only the number and depth of the indexes.

No, it is not that unusual to have the index file bigger. Just make sure
that every index you have is justified by the queries you are making against
the table.

- md


On Fri, Oct 7, 2011 at 4:26 AM, Tompkins Neil
wrote:

> Is it normal practice for a heavily queried MYSQL tables to have a index
> file bigger than the data file ?
>
>
> On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman wrote:
>
>> 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@googlemail.com> 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:
>>>
>>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.html
>>>
>>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil <
>>> 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.
>>>
>>>
>>
>>
>> --
>> - michael dykman
>> - mdykman@gmail.com
>>
>> May the Source be with you.
>>
>
>


--
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--0015175dd79ca3fbba04aeb7af4b--

Re: MySQL Indexes

--------------enigD07FE3D9D06A50B8505738B5
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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. I=
f
> 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.
>=20
> On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps wrote=
:
>=20
>> 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 shoul=
d
>>> 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 =3D '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 optim=
al
>>>>> 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 on=
ly
>>>>> 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 find=
ing
>>>>> is
>>>>> iterative). Perhaps you should consider this approach instead:
>>>>> >>>>> natural-language.html xt-natural-language.html>
>>>>>>
>>>>> http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
>>>>> natural-language.html xt-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 obvious=
ly
>>>>>> primary
>>>>>> keys. We then have some queries using JOIN statements that run sl=
owly
>>>>>> than
>>>>>> we wanted. How many indexes are recommended per table ? For exam=
ple
>>>>>> should
>>>>>> I have a index on all fields that will be used in a WHERE statemen=
t ?
>>>>>> 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=3Dmdykman@gmai=
l.com
>>
>>
>=20
>=20

--=20

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


--------------enigD07FE3D9D06A50B8505738B5
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk6PJukACgkQhmBjz394AnnpLACglEd5wqnq48vO9uFppW82 acI2
6BIAnjtT+4VD3d5RD1FDFPfQCRBB+bQs
=BHR0
-----END PGP SIGNATURE-----

--------------enigD07FE3D9D06A50B8505738B5--

Re: MySQL Indexes

--000e0cd139ba614d0a04aeb8b842
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 wrot=
e:

> 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. I=
f
> > 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 shoul=
d
> >>> 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 =3D '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 optim=
al
> >>>>> 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 on=
ly
> >>>>> 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<
> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.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-lang uage.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 obvious=
ly
> >>>>>> 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 statemen=
t
> ?
> >>>>>> 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=3Dmdykman@gmail.com<
> http://lists.mysql.com/mysql?unsub=3Dmdykman@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
>
>


--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--000e0cd139ba614d0a04aeb8b842--

Re: MySQL Indexes

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

On 7 Oct 2011, at 18:24, Michael Dykman 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.
>=20
> - md
>=20
> On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald wro=
te:
>=20
>> but could this not be called a bug?
>>=20
>> 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.
>>>=20
>>> On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps wrote:=

>>>=20
>>>> 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)?
>>>>=20
>>>>=20
>>>> On 10/06/2011 07:43 PM, Nuno Tavares wrote:
>>>>=20
>>>>> Neil, whenever you see multiple fields you'd like to index, you should=

>>>>> consider, at least:
>>>>>=20
>>>>> * The frequency of each query;
>>>>> * The occurrences of the same field in multiple queries;
>>>>> * The cardinality of each field;
>>>>>=20
>>>>> 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....
>>>>>=20
>>>>> 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).
>>>>>=20
>>>>> -NT
>>>>>=20
>>>>>=20
>>>>> Em 07-10-2011 00:22, Michael Dykman escreveu:
>>>>>=20
>>>>>> Only one index at a time can be used per query, so neither strategy i=
s
>>>>>> optimal. You need at look at the queries you intend to run against
>> the
>>>>>> system and construct indexes which support them.
>>>>>>=20
>>>>>> - md
>>>>>>=20
>>>>>> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins
>>>>>> **wrote:
>>>>>>=20
>>>>>> Maybe that was a bad example. If the query was name =3D 'Red' what
>> index
>>>>>>> should I create ?
>>>>>>>=20
>>>>>>> Should I create a index of all columns used in each query or have a
>>>>>>> index
>>>>>>> on individual column ?
>>>>>>>=20
>>>>>>>=20
>>>>>>> On 6 Oct 2011, at 17:28, Michael Dykman wrote:
>>>>>>>=20
>>>>>>> For the first query, the obvious index on score will give you optima=
l
>>>>>>> results.
>>>>>>>=20
>>>>>>> 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 onl=
y
>>>>>>> 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<
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.html>
>>>>>>>>=20
>>>>>>> http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
>>>>>>> natural-language.html<
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.html>
>>>>>>>=20
>>>>>>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil< >>>>>>> googlemail.com >
>>>>>>> neil.tompkins@googlemail.com> wrote:
>>>>>>>=20
>>>>>>> Hi,
>>>>>>>>=20
>>>>>>>> Can anyone help and offer some advice with regards MySQL indexes.
>>>>>>>> Basically
>>>>>>>> we have a number of different tables all of which have the obviousl=
y
>>>>>>>> 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
>>>>>>>>=20
>>>>>>>> SELECT auto_id, name, score
>>>>>>>> FROM test_table
>>>>>>>> WHERE score> 10
>>>>>>>> ORDER BY score DESC
>>>>>>>>=20
>>>>>>>>=20
>>>>>>>> SELECT auto_id, name, score
>>>>>>>> FROM test_table
>>>>>>>> WHERE score> 10
>>>>>>>> AND name Like '%Red%'
>>>>>>>> ORDER BY score DESC
>>>>>>>>=20
>>>>>>>> How many indexes should be created for these two queries ?
>>>>>>>>=20
>>>>>>>> Thanks,
>>>>>>>> Neil
>>>>>>>>=20
>>>>>>>>=20
>>>>>>>=20
>>>>>>>=20
>>>>>>> --
>>>>>>> - michael dykman
>>>>>>> -mdykman@**gmail.com
>>>>>>>=20
>>>>>>> May the Source be with you.
>>>>>>>=20
>>>>>>>=20
>>>>>>>=20
>>>>>>=20
>>>>>>=20
>>>>>=20
>>>>>=20
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:
>> http://lists.mysql.com/mysql?**unsub=3Dmdykman@gmail.com<
>> http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail.com>
>>>>=20
>>>>=20
>>>=20
>>>=20
>>=20
>> --
>>=20
>> 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/
>>=20
>> http://www.thelounge.net/signature.asc.what.htm
>>=20
>>=20
>=20
>=20
> --=20
> - michael dykman
> - mdykman@gmail.com
>=20
> 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQL Indexes

--Apple-Mail-3-404285938
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

Do you have any good documentation with regards creating indexes. Also infor=
mation for explain statement and what would be the desired result of the exp=
lain statement?

On 7 Oct 2011, at 17:10, Michael Dykman wrote:

> How heavily a given table is queried does not directly affect the index si=
ze, only the number and depth of the indexes.
>=20
> No, it is not that unusual to have the index file bigger. Just make sure t=
hat every index you have is justified by the queries you are making against t=
he table.
>=20
> - md
>=20
>=20
> On Fri, Oct 7, 2011 at 4:26 AM, Tompkins Neil m> wrote:
> Is it normal practice for a heavily queried MYSQL tables to have a index f=
ile bigger than the data file ?
>=20
>=20
> On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman wrote:=

> Only one index at a time can be used per query, so neither strategy is opt=
imal. You need at look at the queries you intend to run against the system a=
nd construct indexes which support them.
>=20
> - md
>=20
> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins m> wrote:
> Maybe that was a bad example. If the query was name =3D 'Red' what index s=
hould I create ?
>=20
> Should I create a index of all columns used in each query or have a index o=
n individual column ?
>=20
>=20
> On 6 Oct 2011, at 17:28, Michael Dykman wrote:
>=20
>> For the first query, the obvious index on score will give you optimal res=
ults.
>>=20
>> The second query is founded on this phrase: "Like '%Red%' " and no index w=
ill help you there. This is an anti-pattern, I am afraid. The only way you=
r database can satisfy that expression is to test each and every record in t=
he that database (the test itself being expensive as infix finding is iterat=
ive). Perhaps you should consider this approach instead:
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.html
>>=20
>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil com> wrote:
>> Hi,
>>=20
>> Can anyone help and offer some advice with regards MySQL indexes. Basica=
lly
>> we have a number of different tables all of which have the obviously prim=
ary
>> keys. We then have some queries using JOIN statements that run slowly th=
an
>> we wanted. How many indexes are recommended per table ? For example sho=
uld
>> 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
>>=20
>> SELECT auto_id, name, score
>> FROM test_table
>> WHERE score > 10
>> ORDER BY score DESC
>>=20
>>=20
>> SELECT auto_id, name, score
>> FROM test_table
>> WHERE score > 10
>> AND name Like '%Red%'
>> ORDER BY score DESC
>>=20
>> How many indexes should be created for these two queries ?
>>=20
>> Thanks,
>> Neil
>>=20
>>=20
>>=20
>> --=20
>> - michael dykman
>> - mdykman@gmail.com
>>=20
>> May the Source be with you.
>=20
>=20
>=20
> --=20
> - michael dykman
> - mdykman@gmail.com
>=20
> May the Source be with you.
>=20
>=20
>=20
>=20
> --=20
> - michael dykman
> - mdykman@gmail.com
>=20
> May the Source be with you.

--Apple-Mail-3-404285938--

Re: MySQL Indexes

--000e0cd139ba7ffe9704aeba20c1
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

The second index you specified '(field_b, field_a)' would be usable when
querying on field_b alone, or both fields in conjunction. This particular
index is of no value should you be querying 'field_a' alone. Then that
first index '(field_a, field_b)' would apply.

- md

On Fri, Oct 7, 2011 at 2:55 PM, Neil Tompkins
wrote:

> Can you give more information as to why the second index would be of no u=
se
> ?
>
> On 7 Oct 2011, at 18:24, Michael Dykman 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 > >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
> 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 goo=
d
> >>>>> candidates - I've seen it somewhere, I believe....
> >>>>>
> >>>>> Just remember that idx_a(field_a,field_b) is not the same, and is n=
ot
> >>>>> 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 strateg=
y
> is
> >>>>>> optimal. You need at look at the queries you intend to run agains=
t
> >> 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 =3D 'Red' wha=
t
> >> 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 n=
o
> >> 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 eve=
ry
> >>>>>>> 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<
> >> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-lang uage.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-lang uage.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=3Dmdykman@gmail.com<
> >> http://lists.mysql.com/mysql?unsub=3Dmdykman@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
> >>
> >>
> >
> >
> > --
> > - michael dykman
> > - mdykman@gmail.com
> >
> > May the Source be with you.
>



--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--000e0cd139ba7ffe9704aeba20c1--

Re: MySQL Indexes

At 01:58 PM 10/7/2011, you wrote:
>Do you have any good documentation with regards creating indexes.
>Also information for explain statement and what would be the desired
>result of the explain statement?


This might help:
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

http://www.sitepoint.com/optimizing-mysql-application/

http://hackmysql.com/case2


There is one more advantage about compound indexes like "index on
(field_a, field_b)". If you are retrieving just field_a and field_b,
in a select statement :

select field_a, field_b from table1 where field_a="abc"

even though you only references field_a in the Where clause, it of
course uses that index to find rows with field_a="abc", but it also
retrieves field_b from the SAME index so MySQL doesn't have to go to
the data file to get field_b. This can dramatically reduce disk I/O
in heavily used queries and occasionally you may want to create a
compound index even though the second field won't be used in a Where clause.

There is a "yin and yang" approach to creating indexes. Newbies will
try and index all possible columns that are used in a Where clause
which results in a huge index file and very slow table updates. The
more indexes you have on a table, the slower it takes to add or
update a row. You really only want to index the columns of the most
frequent queries.

As to which fields to index, on a test database I would remove all
indexes from the table except for the primary keys and have the slow
query log turned on. Run your queries for an hour and examine the
slow query log to see which queries are slow. Copy and paste the
slow Select query to a MySQL administrator like SqlYog Community
Edition v9.2 (http://code.google.com/p/sqlyog/downloads/list) and do
an explain on the query to see what indexes it is (not) using. Then
alter the table and add an index to try and speed up the query. You
may have to repeat this several times to finally get the proper index
defined. Remember to "Reset Query Cache" between tests. Only by
judiciously adding indexes one by one and testing the performance,
will you have the proper "yin and yang" so your tables are in harmony.

Mike
(If you can't achieve harmony, then buy more hardware.)


--
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