MySQL Performance: MySQL => INDEX on DATETIME column

INDEX on DATETIME column [message #926]

Wed, 14 March 2007 11:33

redrain
Messages: 16
Registered: March 2007

Junior Member

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 10

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;
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
| 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...

Report message to a moderator