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=arch...@jab.org