combined or single indexes?

Is there a benefit to a combined index on a table? Or is multiple single
column indexes better?

If I have table 'foo' with columns a, b, and c. I will have a query
like:
select c from foo where a in (1,2,3) and b < 12345;

Is index on a,b better in any way than an a index and a b index?
An explain with one index sees it but doesn't use it (only the where)
and having 2 indexes sees both and uses the one on b.

Am I right to think that 2 indexes are better than one combined one?

thx,
Bryancan



--
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: combined or single indexes?

Hi Bryan, all!


Bryan Cantwell wrote:
> Is there a benefit to a combined index on a table? Or is multiple s=
ingle
> column indexes better?

This is a FAQ, but I'm not aware of a place to point you for the answ=
er.

>=20
> If I have table 'foo' with columns a, b, and c. I will have a query
> like:
> select c from foo where a in (1,2,3) and b < 12345;
>=20
> Is index on a,b better in any way than an a index and a b index?

Any multi-column index can only be used when the values for the leadi=
ng
column(s) is/are known (in your example, they are).

My standard example is a phone book:
It is sorted by "last name", "first name"; you cannot use this order
when the last name in unknown (you have to sequentially scan it).


> An explain with one index sees it but doesn't use it (only the wher=
e)=20
> and having 2 indexes sees both and uses the one on b.

Testing select strategies requires that you have a meaningful amount =
of
data, and a close-to-real distribution of values:

If your tables hold too few rows, the system will notice that it is
wasteful to access them via the index, a scan is faster.
And if your value distribution differs too much from later "real" dat=
a,
the strategy selected will also differ.

>=20
> Am I right to think that 2 indexes are better than one combined one=
?

"It depends":
AFAIK, MySQL will not yet combine several indexes, but evaluate only =
one
per table access.
If you have a usable multi-column index, it will provide better
selectivity than a single-column index, so it is "better" if all the
leading values are given.

I cannot specifically comment on conditions using "in" and "<".


HTH,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berl=
in
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ve=
n
Amtsgericht Muenchen: HRA 95603


--
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: combined or single indexes?

Hi,

MySQL can use a single index in a query as you've seen in the result of
explain.
Of course it is better to have an index made of 2 or more columns because it
will match better the query.

But if I remember well, the in() function can't use an index.
And I think it also can't use an index if you use OR operators like:

select foo from table where a=1 or a=2;

So for your query the single-column index for the second column is enough.

I've seen some tricks for using a faster method by using union and 2-column
index, something like:

select foo from table where a=1 and b<1234
union
select foo from table where a=2 and b<1234
union
select foo from table where a=3 and b<1234

This might be faster in some cases because the query would be able to use
the 2-column index, and especially if the content of those columns is made
only of numbers, because in that case the query will use only the index,
without getting data from the table.

--
Octavian

----- Original Message -----
From: "Bryan Cantwell"
To:
Sent: Tuesday, July 06, 2010 6:41 PM
Subject: combined or single indexes?


> Is there a benefit to a combined index on a table? Or is multiple single
> column indexes better?
>
> If I have table 'foo' with columns a, b, and c. I will have a query
> like:
> select c from foo where a in (1,2,3) and b < 12345;
>
> Is index on a,b better in any way than an a index and a b index?
> An explain with one index sees it but doesn't use it (only the where)
> and having 2 indexes sees both and uses the one on b.
>
> Am I right to think that 2 indexes are better than one combined one?
>
> thx,
> Bryancan
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=octavian.rasnita@ssifbrok er.ro
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 5257 (20100707) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>


__________ Information from ESET NOD32 Antivirus, version of virus signature database 5257 (20100707) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




--
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: combined or single indexes?

How many indexes are recommended per table ??



On 7 Jul 2010, at 06:06, "Octavian Rasnita" > wrote:

> Hi,
>
> MySQL can use a single index in a query as you've seen in the result
> of explain.
> Of course it is better to have an index made of 2 or more columns
> because it will match better the query.
>
> But if I remember well, the in() function can't use an index.
> And I think it also can't use an index if you use OR operators like:
>
> select foo from table where a=1 or a=2;
>
> So for your query the single-column index for the second column is
> enough.
>
> I've seen some tricks for using a faster method by using union and 2-
> column index, something like:
>
> select foo from table where a=1 and b<1234
> union
> select foo from table where a=2 and b<1234
> union
> select foo from table where a=3 and b<1234
>
> This might be faster in some cases because the query would be able
> to use the 2-column index, and especially if the content of those
> columns is made only of numbers, because in that case the query will
> use only the index, without getting data from the table.
>
> --
> Octavian
>
> ----- Original Message ----- From: "Bryan Cantwell" > >
> To:
> Sent: Tuesday, July 06, 2010 6:41 PM
> Subject: combined or single indexes?
>
>
>> Is there a benefit to a combined index on a table? Or is multiple
>> single
>> column indexes better?
>>
>> If I have table 'foo' with columns a, b, and c. I will have a query
>> like:
>> select c from foo where a in (1,2,3) and b < 12345;
>>
>> Is index on a,b better in any way than an a index and a b index?
>> An explain with one index sees it but doesn't use it (only the where)
>> and having 2 indexes sees both and uses the one on b.
>>
>> Am I right to think that 2 indexes are better than one combined one?
>>
>> thx,
>> Bryancan
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=octavian.rasnita@ssifbrok er.ro
>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus
>> signature database 5257 (20100707) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 5257 (20100707) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompkins@googlemail. com
>

--
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: combined or single indexes?

--0003255753526ab86b048aed4479
Content-Type: text/plain; charset=ISO-8859-1

As many as you need, but no more :-)

The right indexes give you a boost in select performance, but every index
also needs to be updated when your data changes.



On Thu, Jul 8, 2010 at 11:25 PM, Neil Tompkins > wrote:

> How many indexes are recommended per table ??
>
>
>
>
> On 7 Jul 2010, at 06:06, "Octavian Rasnita" <
> octavian.rasnita@ssifbroker.ro> wrote:
>
> Hi,
>>
>> MySQL can use a single index in a query as you've seen in the result of
>> explain.
>> Of course it is better to have an index made of 2 or more columns because
>> it will match better the query.
>>
>> But if I remember well, the in() function can't use an index.
>> And I think it also can't use an index if you use OR operators like:
>>
>> select foo from table where a=1 or a=2;
>>
>> So for your query the single-column index for the second column is enough.
>>
>> I've seen some tricks for using a faster method by using union and
>> 2-column index, something like:
>>
>> select foo from table where a=1 and b<1234
>> union
>> select foo from table where a=2 and b<1234
>> union
>> select foo from table where a=3 and b<1234
>>
>> This might be faster in some cases because the query would be able to use
>> the 2-column index, and especially if the content of those columns is made
>> only of numbers, because in that case the query will use only the index,
>> without getting data from the table.
>>
>> --
>> Octavian
>>
>> ----- Original Message ----- From: "Bryan Cantwell" <
>> bcantwell@firescope.com>
>> To:
>> Sent: Tuesday, July 06, 2010 6:41 PM
>> Subject: combined or single indexes?
>>
>>
>> Is there a benefit to a combined index on a table? Or is multiple single
>>> column indexes better?
>>>
>>> If I have table 'foo' with columns a, b, and c. I will have a query
>>> like:
>>> select c from foo where a in (1,2,3) and b < 12345;
>>>
>>> Is index on a,b better in any way than an a index and a b index?
>>> An explain with one index sees it but doesn't use it (only the where)
>>> and having 2 indexes sees both and uses the one on b.
>>>
>>> Am I right to think that 2 indexes are better than one combined one?
>>>
>>> thx,
>>> Bryancan
>>>
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=octavian.rasnita@ssifbrok er.ro
>>>
>>>
>>> __________ Information from ESET NOD32 Antivirus, version of virus
>>> signature database 5257 (20100707) __________
>>>
>>> The message was checked by ESET NOD32 Antivirus.
>>>
>>> http://www.eset.com
>>>
>>>
>>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus
>> signature database 5257 (20100707) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=neil.tompkins@googlemail. com
>>
>>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0003255753526ab86b048aed4479--

Re: combined or single indexes?

--Apple-Mail-1--301450002
Content-Type: text/plain;
charset=us-ascii;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit


Should we be looking to create a index for all fields that we might be
perform a select condition in a where clause for ?

On 9 Jul 2010, at 05:59, Johan De Meersman wrote:

> As many as you need, but no more :-)
>
> The right indexes give you a boost in select performance, but every
> index also needs to be updated when your data changes.
>
>
>
> On Thu, Jul 8, 2010 at 11:25 PM, Neil Tompkins > > wrote:
> How many indexes are recommended per table ??
>
>
>
>
> On 7 Jul 2010, at 06:06, "Octavian Rasnita" > > wrote:
>
> Hi,
>
> MySQL can use a single index in a query as you've seen in the result
> of explain.
> Of course it is better to have an index made of 2 or more columns
> because it will match better the query.
>
> But if I remember well, the in() function can't use an index.
> And I think it also can't use an index if you use OR operators like:
>
> select foo from table where a=1 or a=2;
>
> So for your query the single-column index for the second column is
> enough.
>
> I've seen some tricks for using a faster method by using union and 2-
> column index, something like:
>
> select foo from table where a=1 and b<1234
> union
> select foo from table where a=2 and b<1234
> union
> select foo from table where a=3 and b<1234
>
> This might be faster in some cases because the query would be able
> to use the 2-column index, and especially if the content of those
> columns is made only of numbers, because in that case the query will
> use only the index, without getting data from the table.
>
> --
> Octavian
>
> ----- Original Message ----- From: "Bryan Cantwell" > >
> To:
> Sent: Tuesday, July 06, 2010 6:41 PM
> Subject: combined or single indexes?
>
>
> Is there a benefit to a combined index on a table? Or is multiple
> single
> column indexes better?
>
> If I have table 'foo' with columns a, b, and c. I will have a query
> like:
> select c from foo where a in (1,2,3) and b < 12345;
>
> Is index on a,b better in any way than an a index and a b index?
> An explain with one index sees it but doesn't use it (only the where)
> and having 2 indexes sees both and uses the one on b.
>
> Am I right to think that 2 indexes are better than one combined one?
>
> thx,
> Bryancan
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=octavian.rasnita@ssifbrok er.ro
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 5257 (20100707) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 5257 (20100707) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompkins@googlemail. com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel

--Apple-Mail-1--301450002--

Re: combined or single indexes?

--0016e644df0aeaaa53048aede168
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Jul 9, 2010 at 7:30 AM, Neil Tompkins
wrote:

>
> Should we be looking to create a index for all fields that we might be
> perform a select condition in a where clause for ?


It's a bit of trial and error, you have to weight the number of times you
select based on a potential index vs. the impact that index might have for
the amount of updates you do on the table.

Generally speaking, though, it's indeed a good idea to find the
most-frequently used where-clauses and index them.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016e644df0aeaaa53048aede168--

Re: combined or single indexes?

At 12:43 AM 7/9/2010, you wrote:
>On Fri, Jul 9, 2010 at 7:30 AM, Neil Tompkins
>wrote:
>
> >
> > Should we be looking to create a index for all fields that we might be
> > perform a select condition in a where clause for ?

Neil,
Keep in mind that the more indexes you add to a table, the slower
your inserts will become because it needs to maintain the indexes. I would
only consider adding indexes to the slow queries. You can use the Explain
on your Select statement to see which indexes are being used. See
http://www.databasejournal.com/features/mysql/article.php/13 82791/Optimizing-MySQL-Queries-and-Indexes.htm
for an explanation on how to use EXPLAIN.

BTW, if you are indexing text columns, you may want to look at FullText
indexing.

Mike



>It's a bit of trial and error, you have to weight the number of times you
>select based on a potential index vs. the impact that index might have for
>the amount of updates you do on the table.
>
>Generally speaking, though, it's indeed a good idea to find the
>most-frequently used where-clauses and index them.
>
>
>--
>Bier met grenadyn
>Is als mosterd by den wyn
>Sy die't drinkt, is eene kwezel
>Hy die't drinkt, is ras een ezel


--
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: combined or single indexes?

--0016e644d5ae16973b048be8c30e
Content-Type: text/plain; charset=ISO-8859-1

Hi

So Just running a basic query I get returned the following :

table,type,possible_keys,key,key_len,ref,rows,Extra,
Products,ALL,,,,,9884,where used,

Therefore, I assume "*ALL*" is the worst possible type and should look at
adding a an index to this particular field ?

Cheers
Neil


On Fri, Jul 9, 2010 at 3:31 PM, mos wrote:

> At 12:43 AM 7/9/2010, you wrote:
>
>> On Fri, Jul 9, 2010 at 7:30 AM, Neil Tompkins
>> wrote:
>>
>> >
>> > Should we be looking to create a index for all fields that we might be
>> > perform a select condition in a where clause for ?
>>
>
> Neil,
> Keep in mind that the more indexes you add to a table, the slower your
> inserts will become because it needs to maintain the indexes. I would only
> consider adding indexes to the slow queries. You can use the Explain on your
> Select statement to see which indexes are being used. See
> http://www.databasejournal.com/features/mysql/article.php/13 82791/Optimizing-MySQL-Queries-and-Indexes.htmfor an explanation on how to use EXPLAIN.
>
> BTW, if you are indexing text columns, you may want to look at FullText
> indexing.
>
> Mike
>
>
>
>
> It's a bit of trial and error, you have to weight the number of times you
>> select based on a potential index vs. the impact that index might have for
>> the amount of updates you do on the table.
>>
>> Generally speaking, though, it's indeed a good idea to find the
>> most-frequently used where-clauses and index them.
>>
>>
>> --
>> Bier met grenadyn
>> Is als mosterd by den wyn
>> Sy die't drinkt, is eene kwezel
>> Hy die't drinkt, is ras een ezel
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompkins@googlemail. com
>
>

--0016e644d5ae16973b048be8c30e--

Re: combined or single indexes?

On 7/21/2010 1:02 PM, Tompkins Neil wrote:
> Hi
>
> So Just running a basic query I get returned the following :
>
> table,type,possible_keys,key,key_len,ref,rows,Extra,
> Products,ALL,,,,,9884,where used,
>
> Therefore, I assume "*ALL*" is the worst possible type and should look at
> adding a an index to this particular field ?
>

Why assume when the manual is right there to remove all doubt?

http://dev.mysql.com/doc/refman/5.1/en/explain-output.html
####
ALL

A full table scan is done for each combination of rows from the previous
tables. This is normally not good if the table is the first table not
marked const, and usually very bad in all other cases. Normally, you can
avoid ALL by adding indexes that enable row retrieval from the table
based on constant values or column values from earlier tables.
####




--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
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: combined or single indexes?

--001636c92c695a2d2c048bff106b
Content-Type: text/plain; charset=ISO-8859-1

Thanks for your reply, and sorry for not verifying in the manual. Another
couple of questions I have :

If I run a EXPLAIN query and SELECT against a primary key and SELECT fields
which are not indexed, I assume that returned EXPLAIN statement as below,
means I don't need to index additional fields providing the PRIMARY KEY is
included in the SELECT statement ?

table,type,possible_keys,key,key_len,ref,rows,Extra,
Products,const,PRIMARY,PRIMARY,8,const,1,,

Also, if I want to add a index to an existing table containing 9000 records,
how long should I expect this to take ? Is it instant ?

Cheers
Neil


On Thu, Jul 22, 2010 at 5:20 PM, Shawn Green (MySQL) <
shawn.l.green@oracle.com> wrote:

> On 7/21/2010 1:02 PM, Tompkins Neil wrote:
>
>> Hi
>>
>> So Just running a basic query I get returned the following :
>>
>> table,type,possible_keys,key,key_len,ref,rows,Extra,
>> Products,ALL,,,,,9884,where used,
>>
>> Therefore, I assume "*ALL*" is the worst possible type and should look at
>> adding a an index to this particular field ?
>>
>>
> Why assume when the manual is right there to remove all doubt?
>
> http://dev.mysql.com/doc/refman/5.1/en/explain-output.html
> ####
> ALL
>
> A full table scan is done for each combination of rows from the previous
> tables. This is normally not good if the table is the first table not marked
> const, and usually very bad in all other cases. Normally, you can avoid ALL
> by adding indexes that enable row retrieval from the table based on constant
> values or column values from earlier tables.
> ####
>
>
>
>
> --
> Shawn Green
> MySQL Principle Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN
>

--001636c92c695a2d2c048bff106b--

RE: combined or single indexes?

>-----Original Message-----
>From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
>Sent: Thursday, July 22, 2010 3:39 PM
>To: Shawn Green (MySQL)
>Cc: mysql@lists.mysql.com
>Subject: Re: combined or single indexes?
>
>Thanks for your reply, and sorry for not verifying in the manual. Another
>couple of questions I have :
>
>If I run a EXPLAIN query and SELECT against a primary key and SELECT fields
>which are not indexed, I assume that returned EXPLAIN statement as below,
>means I don't need to index additional fields providing the PRIMARY KEY is
>included in the SELECT statement ?
>
>table,type,possible_keys,key,key_len,ref,rows,Extra,
>Products,const,PRIMARY,PRIMARY,8,const,1,,
>
[JS] Your posts will be more legible if you use "\G" instead of ";" at the end
of an EXPLAIN.

As for the indexing, if you only need one key then you only need one key. Just
remember that when you test things with sample data, MySQL might make
surprising decisions based upon the amount of data. You'll only really know
what will happen if you have a substantial data set.

>Also, if I want to add a index to an existing table containing 9000 records,
>how long should I expect this to take ? Is it instant ?
>
[JS] Faster than you can type, I should think.

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.the-infoshop.com



>Cheers
>Neil
>
>
>On Thu, Jul 22, 2010 at 5:20 PM, Shawn Green (MySQL) <
>shawn.l.green@oracle.com> wrote:
>
>> On 7/21/2010 1:02 PM, Tompkins Neil wrote:
>>
>>> Hi
>>>
>>> So Just running a basic query I get returned the following :
>>>
>>> table,type,possible_keys,key,key_len,ref,rows,Extra,
>>> Products,ALL,,,,,9884,where used,
>>>
>>> Therefore, I assume "*ALL*" is the worst possible type and should look at
>>> adding a an index to this particular field ?
>>>
>>>
>> Why assume when the manual is right there to remove all doubt?
>>
>> http://dev.mysql.com/doc/refman/5.1/en/explain-output.html
>> ####
>> ALL
>>
>> A full table scan is done for each combination of rows from the previous
>> tables. This is normally not good if the table is the first table not
>> marked
>> const, and usually very bad in all other cases. Normally, you can avoid ALL
>> by adding indexes that enable row retrieval from the table based on
>> constant
>> values or column values from earlier tables.
>> ####
>>
>>
>>
>>
>> --
>> Shawn Green
>> MySQL Principle Technical Support Engineer
>> Oracle USA, Inc.
>> Office: Blountville, TN
>>




--
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: combined or single indexes?

--00163691fc662f2b74048c0011c5
Content-Type: text/plain; charset=ISO-8859-1

Thanks for the information Jerry. Just to confirm, you mentioned "*if you
only need one key then you only need one key*". My question was that this
particular query was using SELECT against a primary key and other fields
which are NOT indexed. The EXPLAIN result was

table,type,possible_keys,key,key_len,ref,rows,Extra,
Products,const,PRIMARY,PRIMARY,8,const,1,,

So from this do I assume that if I'm always searching the PRIMARY KEY, that
I don't need to index the other fields ?

Cheers
Neil

On Thu, Jul 22, 2010 at 9:27 PM, Jerry Schwartz wrote:

> >-----Original Message-----
> >From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> >Sent: Thursday, July 22, 2010 3:39 PM
> >To: Shawn Green (MySQL)
> >Cc: mysql@lists.mysql.com
> >Subject: Re: combined or single indexes?
> >
> >Thanks for your reply, and sorry for not verifying in the manual. Another
> >couple of questions I have :
> >
> >If I run a EXPLAIN query and SELECT against a primary key and SELECT
> fields
> >which are not indexed, I assume that returned EXPLAIN statement as below,
> >means I don't need to index additional fields providing the PRIMARY KEY is
> >included in the SELECT statement ?
> >
> >table,type,possible_keys,key,key_len,ref,rows,Extra,
> >Products,const,PRIMARY,PRIMARY,8,const,1,,
> >
> [JS] Your posts will be more legible if you use "\G" instead of ";" at the
> end
> of an EXPLAIN.
>
> As for the indexing, if you only need one key then you only need one key.
> Just
> remember that when you test things with sample data, MySQL might make
> surprising decisions based upon the amount of data. You'll only really know
> what will happen if you have a substantial data set.
>
> >Also, if I want to add a index to an existing table containing 9000
> records,
> >how long should I expect this to take ? Is it instant ?
> >
> [JS] Faster than you can type, I should think.
>
> 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.the-infoshop.com
>
>
>
> >Cheers
> >Neil
> >
> >
> >On Thu, Jul 22, 2010 at 5:20 PM, Shawn Green (MySQL) <
> >shawn.l.green@oracle.com> wrote:
> >
> >> On 7/21/2010 1:02 PM, Tompkins Neil wrote:
> >>
> >>> Hi
> >>>
> >>> So Just running a basic query I get returned the following :
> >>>
> >>> table,type,possible_keys,key,key_len,ref,rows,Extra,
> >>> Products,ALL,,,,,9884,where used,
> >>>
> >>> Therefore, I assume "*ALL*" is the worst possible type and should look
> at
> >>> adding a an index to this particular field ?
> >>>
> >>>
> >> Why assume when the manual is right there to remove all doubt?
> >>
> >> http://dev.mysql.com/doc/refman/5.1/en/explain-output.html
> >> ####
> >> ALL
> >>
> >> A full table scan is done for each combination of rows from the previous
> >> tables. This is normally not good if the table is the first table not
> >> marked
> >> const, and usually very bad in all other cases. Normally, you can avoid
> ALL
> >> by adding indexes that enable row retrieval from the table based on
> >> constant
> >> values or column values from earlier tables.
> >> ####
> >>
> >>
> >>
> >>
> >> --
> >> Shawn Green
> >> MySQL Principle Technical Support Engineer
> >> Oracle USA, Inc.
> >> Office: Blountville, TN
> >>
>
>
>
>

--00163691fc662f2b74048c0011c5--

RE: combined or single indexes?

------=_NextPart_000_0001_01CB29C2.EC6000E0
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]=20
Sent: Thursday, July 22, 2010 4:50 PM
To: Jerry Schwartz
Cc: Shawn Green (MySQL); mysql@lists.mysql.com
Subject: Re: combined or single indexes?

=20

Thanks for the information Jerry. Just to confirm, you mentioned "if =
you only need one key then you only need one key". My question was that =
this particular query was using SELECT against a primary key and other =
fields which are NOT indexed. The EXPLAIN result was

=20

table,type,possible_keys,key,key_len,ref,rows,Extra,
Products,const,PRIMARY,PRIMARY,8,const,1,,

=20

So from this do I assume that if I'm always searching the PRIMARY KEY, =
that I don't need to index the other fields ?

=20

[JS] I think I must have missed the start of this thread, because I =
donâ€=99t remember seeing the original query. The answer lies in =
your WHERE clause, and in the number of records that would potentially =
qualify. MySQL will ignore keys and do a full table scan if it decides =
that none of the keys would eliminate a big portion of the records. =
(This is why I warned about small sample datasets.) If your query looks =
like

=20

â€=A6 WHERE `account_num` =3D 17 â€=A6

=20

and account numbers are unique, then an index on `account_num` should be =
enough. If you are always and ONLY searching on the primary key, then =
the primary key is all you need. Thatâ€=99s usually not the case, =
though. Youâ€=99re probably going to want to search on other things, =
sooner or later.

=20

Iâ€=99m not an expert on optimizing queries in MySQL, and there are =
probably differences between the storage engines, but I hope this helps.

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: jerry@gii.co.jp=20

Web site: www.the-infoshop.com =20

=20

=20

=20

Cheers

Neil

On Thu, Jul 22, 2010 at 9:27 PM, Jerry Schwartz wrote:

>-----Original Message-----
>From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
>Sent: Thursday, July 22, 2010 3:39 PM
>To: Shawn Green (MySQL)
>Cc: mysql@lists.mysql.com
>Subject: Re: combined or single indexes?
>
>Thanks for your reply, and sorry for not verifying in the manual. =
Another
>couple of questions I have :
>
>If I run a EXPLAIN query and SELECT against a primary key and SELECT =
fields
>which are not indexed, I assume that returned EXPLAIN statement as =
below,
>means I don't need to index additional fields providing the PRIMARY KEY =
is
>included in the SELECT statement ?
>
>table,type,possible_keys,key,key_len,ref,rows,Extra,
>Products,const,PRIMARY,PRIMARY,8,const,1,,
>

[JS] Your posts will be more legible if you use "\G" instead of ";" at =
the end
of an EXPLAIN.

As for the indexing, if you only need one key then you only need one =
key. Just
remember that when you test things with sample data, MySQL might make
surprising decisions based upon the amount of data. You'll only really =
know
what will happen if you have a substantial data set.


>Also, if I want to add a index to an existing table containing 9000 =
records,
>how long should I expect this to take ? Is it instant ?
>

[JS] Faster than you can type, I should think.

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.the-infoshop.com




>Cheers
>Neil
>
>
>On Thu, Jul 22, 2010 at 5:20 PM, Shawn Green (MySQL) <
>shawn.l.green@oracle.com> wrote:
>
>> On 7/21/2010 1:02 PM, Tompkins Neil wrote:
>>
>>> Hi
>>>
>>> So Just running a basic query I get returned the following :
>>>
>>> table,type,possible_keys,key,key_len,ref,rows,Extra,
>>> Products,ALL,,,,,9884,where used,
>>>
>>> Therefore, I assume "*ALL*" is the worst possible type and should =
look at
>>> adding a an index to this particular field ?
>>>
>>>
>> Why assume when the manual is right there to remove all doubt?
>>
>> http://dev.mysql.com/doc/refman/5.1/en/explain-output.html
>> ####
>> ALL
>>
>> A full table scan is done for each combination of rows from the =
previous
>> tables. This is normally not good if the table is the first table not
>> marked
>> const, and usually very bad in all other cases. Normally, you can =
avoid ALL
>> by adding indexes that enable row retrieval from the table based on
>> constant
>> values or column values from earlier tables.
>> ####
>>
>>
>>
>>
>> --
>> Shawn Green
>> MySQL Principle Technical Support Engineer
>> Oracle USA, Inc.
>> Office: Blountville, TN
>>




=20


------=_NextPart_000_0001_01CB29C2.EC6000E0--

Re: combined or single indexes?

--Apple-Mail-1-909027624
Content-Type: text/plain;
charset=utf-8;
format=flowed;
delsp=yes
Content-Transfer-Encoding: quoted-printable

Thanks for the useful information. This is the answer I was. Looking =20
for.

Neil

On 22 Jul 2010, at 22:25, "Jerry Schwartz" wrote:

> From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> Sent: Thursday, July 22, 2010 4:50 PM
> To: Jerry Schwartz
> Cc: Shawn Green (MySQL); mysql@lists.mysql.com
> Subject: Re: combined or single indexes?
>
>
>
> Thanks for the information Jerry. Just to confirm, you mentioned =20
> "if you only need one key then you only need one key". My question =20=

> was that this particular query was using SELECT against a primary =20
> key and other fields which are NOT indexed. The EXPLAIN result was
>
>
>
> table,type,possible_keys,key,key_len,ref,rows,Extra,
> Products,const,PRIMARY,PRIMARY,8,const,1,,
>
>
>
> So from this do I assume that if I'm always searching the PRIMARY =20
> KEY, that I don't need to index the other fields ?
>
>
>
> [JS] I think I must have missed the start of this thread, because I =20=

> donâ€=99t remember seeing the original query. The answer lies in =
your WH=20
> ERE clause, and in the number of records that would potentially qual=20=

> ify. MySQL will ignore keys and do a full table scan if it decides t=20=

> hat none of the keys would eliminate a big portion of the records. (=20=

> This is why I warned about small sample datasets.) If your query loo=20=

> ks like
>
>
>
> â€=A6 WHERE `account_num` =3D 17 â€=A6
>
>
>
> and account numbers are unique, then an index on `account_num` =20
> should be enough. If you are always and ONLY searching on the =20
> primary key, then the primary key is all you need. Thatâ€=99s =
usually no=20
> t the case, though. Youâ€=99re probably going to want to search on =
other=20
> things, sooner or later.
>
>
>
> Iâ€=99m not an expert on optimizing queries in MySQL, and there =
are prob=20
> ably differences between the storage engines, but I hope this helps.
>
>
>
> 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.the-infoshop.com
>
>
>
>
>
>
>
> Cheers
>
> Neil
>
> On Thu, Jul 22, 2010 at 9:27 PM, Jerry Schwartz =20
> wrote:
>
> >-----Original Message-----
> >From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> >Sent: Thursday, July 22, 2010 3:39 PM
> >To: Shawn Green (MySQL)
> >Cc: mysql@lists.mysql.com
> >Subject: Re: combined or single indexes?
> >
> >Thanks for your reply, and sorry for not verifying in the manual. =20=

> Another
> >couple of questions I have :
> >
> >If I run a EXPLAIN query and SELECT against a primary key and =20
> SELECT fields
> >which are not indexed, I assume that returned EXPLAIN statement as =20=

> below,
> >means I don't need to index additional fields providing the PRIMARY =20=

> KEY is
> >included in the SELECT statement ?
> >
> >table,type,possible_keys,key,key_len,ref,rows,Extra,
> >Products,const,PRIMARY,PRIMARY,8,const,1,,
> >
>
> [JS] Your posts will be more legible if you use "\G" instead of ";" =20=

> at the end
> of an EXPLAIN.
>
> As for the indexing, if you only need one key then you only need one =20=

> key. Just
> remember that when you test things with sample data, MySQL might make
> surprising decisions based upon the amount of data. You'll only =20
> really know
> what will happen if you have a substantial data set.
>
>
> >Also, if I want to add a index to an existing table containing 9000 =20=

> records,
> >how long should I expect this to take ? Is it instant ?
> >
>
> [JS] Faster than you can type, I should think.
>
> 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.the-infoshop.com
>
>
>
>
> >Cheers
> >Neil
> >
> >
> >On Thu, Jul 22, 2010 at 5:20 PM, Shawn Green (MySQL) <
> >shawn.l.green@oracle.com> wrote:
> >
> >> On 7/21/2010 1:02 PM, Tompkins Neil wrote:
> >>
> >>> Hi
> >>>
> >>> So Just running a basic query I get returned the following :
> >>>
> >>> table,type,possible_keys,key,key_len,ref,rows,Extra,
> >>> Products,ALL,,,,,9884,where used,
> >>>
> >>> Therefore, I assume "*ALL*" is the worst possible type and =20
> should look at
> >>> adding a an index to this particular field ?
> >>>
> >>>
> >> Why assume when the manual is right there to remove all doubt?
> >>
> >> http://dev.mysql.com/doc/refman/5.1/en/explain-output.html
> >> ####
> >> ALL
> >>
> >> A full table scan is done for each combination of rows from the =20
> previous
> >> tables. This is normally not good if the table is the first table =20=

> not
> >> marked
> >> const, and usually very bad in all other cases. Normally, you can =20=

> avoid ALL
> >> by adding indexes that enable row retrieval from the table based on
> >> constant
> >> values or column values from earlier tables.
> >> ####
> >>
> >>
> >>
> >>
> >> --
> >> Shawn Green
> >> MySQL Principle Technical Support Engineer
> >> Oracle USA, Inc.
> >> Office: Blountville, TN
> >>
>
>
>
>

--Apple-Mail-1-909027624--