Can I put an mysql index on a date field with when I use >= or <=?

Question

I have indexes for my table user,date, and date,user but when I issue queries like

SELECT * FROM table WHERE date >= '2010-5-1' and user='test';

It doesn't use the date,user index it uses the user index. Is there any way to get mysql to use the date,user index for these queries?

Answer 1

Yep, by specifying USE INDEX (index_list), you can tell MySQL to use only one of the named indexes to find rows in the table.

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

That being said, you should probably check to make sure you are actually doing better than the optimizer's decision. There are a lot of complexities behind how a query is optimized and which indexes to use, and sometimes the shape of the data will result in the selection of a seemingly counter-intuitive (but somehow more effective) index.

Answer 2

Using the (date, user) index would probably be a bad idea here. The records you need could be spread over many pages of that index, whereas in the (user, date) index they will be close together.

When giving hints to the optimizer, make sure you don't make things worse. The optimizer usually knows what it is doing.

If you still think the optimizer has chosen an incorrect index you could update the table statistics and see if that helps.

Answer 3

Before resorting to optimizer hints, try analyzing the table.

ANALYZE TABLE table