Mysql: Index on date field not getting used

[anna2003]

Hello,

 

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 

 

thanks

anna

[Barand]

My method would be

 

[php:1:08019e602b]<?php

$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 \";

?>[/php:1:08019e602b]

 

hth

[anna2003]

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?

[Barand]

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.