MySQL can use a single index in a query as you've seen in the result of
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
select foo from table where a=2 and b<1234
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.
----- Original Message -----
From: "Bryan Cantwell" <bcantwell@stripped>
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
> 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?
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 5257 (20100707) __________
> The message was checked by ESET NOD32 Antivirus.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 5257
The message was checked by ESET NOD32 Antivirus.