Jump to content


Photo

Mysql: Index on date field not getting used


  • Please log in to reply
3 replies to this topic

#1 anna2003

anna2003
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 04 November 2003 - 04:14 PM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 04 November 2003 - 08:59 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 anna2003

anna2003
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 04 November 2003 - 09:36 PM

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?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 05 November 2003 - 11:05 AM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users