INDEX on DATETIME column

[redrain]

Hi,

I’m working on a news site. After a problem with MySQL performance I’ve started to rewrite query’s and rebuild my indexes. I started with slow quieries in logs.

SELECT news_id,news_title,news_hit FROM news WHERE DATE(news_date) = ‘2007-03-14’ ORDER BY news_hit DESC LIMIT 10mysql> explain SELECT news_id,news_title,news_hit FROM news WHERE DATE(news_date) = ‘2007-03-14’ ORDER BY news_hit DESC LIMIT 10;±—±------------±------±-----±--------------±-----±--------±-----±------±----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±-----±--------------±-----±--------±-----±------±----------------------------+| 1 | SIMPLE | news | ALL | NULL | NULL | NULL | NULL | 48878 | Using where; Using filesort |±—±------------±------±-----±--------------±-----±--------±-----±------±----------------------------+1 row in set (0.00 sec)

This query is the one of them which gets most readed 10 news of the day. Date stored as DATETIME. So making an index doesn’t work (at least on my table). And it doesn’t use an index. I don’t know how can I solve this. Any idea could help.

Thanks…

[toasty]

MySQL won’t/can’t use indexes where you apply a function to a column in a where condition.

In your case it’s the DATE() function that’s stopping the query use an index.

Off the top of my head instead of:
mysql> explain SELECT news_id,news_title,news_hit FROM news WHERE DATE(news_date) = ‘2007-03-14’ ORDER BY news_hit DESC LIMIT 10;

You could do something a bit hacky such as :
mysql> explain SELECT news_id,news_title,news_hit FROM news WHERE news_date between ‘2007-03-14 00:00:00’ and ‘2007-03-15 00:00:00’ ORDER BY news_hit DESC LIMIT 10;

which would use the index on news_date.

I’m sure there’s a more elegant way to do this but it’s a start!

BTW, depending on your data distribution etc, a multi-col index on (news_date, news_hit) should stop this query needing to filesort to do the order by too.

Toasty