MySQL - lookup based on more indexes

Question

Can MySQL search for table rows using two or more indexes?

I have a table with these columns:

relation:
- type varchar(32)
- id_foo int
- id_bar int

I need to search for rows based on type and either presta_id or vario_id. The queries will look like this:

SELECT id_foo FROM relation WHERE type = 'baz' AND id_bar = 128
SELECT id_bar FROM relation WHERE type = 'qux' AND id_foo = 256

What is the correct way to define indexes for this table and for these search queries?

Should I put separate indexes on all three columns (type, id_foo, id_bar), or is it better to create two multi-column indexes - type + id_foo and type + id_bar?

Answer 1

Yes MySQL can. You can find out yourself by issuing:

EXPLAIN SELECT id_foo FROM relation WHERE type = 'baz' AND id_bar = 128

There MySQL prints which indexes were used.

Multi column indexes (type,id_bar and type,id_foo; you need both in order to speed up both select queries) are a possible way to speed up such queries, however, multi column indexes do not make sense in all cases. E.g., a multi column index on type and id_bar (in this order) doesn't help if you have queries which do not include type.

See http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html and http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

Answer 2

It is better to create two indexes: relation(type, id_bar) and relation(type, id_foo).

In your case, the two columns could be in either order. But you need two indexes to optimize both queries.

MySQL actually has pretty good documentation on the use of composite indexes. See here.

Answer 3

Create an index for each type of WHERE clause you plan to have. One joint index on type and id_bar will help the first query. Another joint index on type and id_foo will help the second.

One index combining all 3 will not help because it's not specific enough to any single query.