1

I have a Query like below

select 
id,name,baseid,member_card_type,membercard_num,last_draw,counter 
from details_dest
where datediff(curdate(),basedate)<100;

i have used the explain on that and found it is using index which is on basedate and i think date_diff is the problem

so please suggest me is there any other way to execute it without any functions

and kindly tell me which is better datediff() or to_days() in according to performance

i am using mysql 5.5

vidyadhar
  • 3,118
  • 6
  • 22
  • 31

1 Answers1

6

I would suggest the following query:

select 
id,name,baseid,member_card_type,membercard_num,last_draw,counter 
from details_dest
where basedate > (curdate() - INTERVAL 100 DAY);
manuskc
  • 784
  • 4
  • 14
  • thanks its working good and quick.....will u please tell me why datadiff() function is slow and there are any other function like datediff() which will degrade the performance..... – vidyadhar Apr 01 '13 at 09:30
  • The reason i think your query was slow was not datediff, but the way datediff was used. `datediff(curdate(),basedate)<100` :: here db would not know for how many rows this condition is true and will have to run datediff for basedate value in every row. Whereas in `basedate > (curdate() - INTERVAL 100 DAY)` :: RHS is calculated once because it is a constant and since basedate is an indexed column, only those rows which meet the condition are processed, hence it is faster. – manuskc Apr 01 '13 at 22:22