Jump to content


This topic is now archived and is closed to further replies.


Mysql: Index on date field not getting used

Recommended Posts



I have a query that is very slow. This query used the date field to find urls that are less than 30 days old.


In order to speed up this query, I indexed the date field. Evem after the indexing the query results are slow and explain on select statement doesn\'t show that the index is getting used. Is there anyway I can make my select statement use the index. Here is my query


SELECT distinct r.res_id, r.resource_name,r.resource_url FROM links l, sections s, resources r WHERE l.sec_id = 62 AND r.res_id = l.res_id AND s.sec_id = l.sec_id AND to_days(now()) - to_days(r.update_date) <= 30 ORDER BY r.resource_name asc 




Share this post

Link to post
Share on other sites

My method would be



$d30 = date(\'Y-m-d\' , strtotime(\'-30 days\'));


$sql = \"SELECT distinct r.res_id, r.resource_name,r.resource_url

FROM (links l INNER JOIN sections s ON l.sec_id = s.sec_id)

INNER JOIN resources r ON l.res_id = r.res_id

WHERE l.sec_id = 62 AND r.update_date > \'$d30\'

ORDER BY r.resource_name \";




Share this post

Link to post
Share on other sites

Thank you Barand, this query is indeed a lot faster than the one I was using but even now when I do an explain, it doesn\'t use my date index? Why not? And why is this query much faster than the one I was using. Can you tell me?

Share this post

Link to post
Share on other sites

Not too sure about the internals of MySql. Does an index on resource_name give it a speed boost, or do you have one already?


I do know that INNER JOINS are more efficient than using WHERE to join tables. I also find it easier to understand when the WHERE criteria are separated form the join criteria. Also it isn\'t doing a date conversion TO_DAYS on every record, just a compare.

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.