How many indexes are recommended per table ??
On 7 Jul 2010, at 06:06, "Octavian Rasnita" <octavian.rasn...@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" <bcantw...@firescope.com
>
To: <mysql@lists.mysql.com>
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.rasn...@ssifbroker.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.tompk...@googlemail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
