| 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...
|
|
|
| Re: INDEX on DATETIME column [message #929 is a reply to message #926 ] |
Thu, 15 March 2007 05:13  |
toasty
Messages: 19
Registered: August 2006
Location: UK |
Junior Member |
|
|
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
[Updated on: Thu, 15 March 2007 05:14]
|
|
|
Members
Search
Help
Register
Login
Home
