Not the answer you need?
Register and ask your own question!

INDEX on DATETIME column

redrainredrain ContributorCurrent User Role Advisor
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)</pre>


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

Comments

  • toastytoasty Contributor Inactive User Role Advisor
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.