fetching a row in the middle of the table

Question

SELECT r.*, u.username 
FROM `reservation` AS r JOIN 
     `users` AS u 
WHERE u.id = r.user_id 
AND   DATE(r.bx_date) >= DATE('2012-08-22') 
AND   DATE(r.bx_date) <= DATE('2012-08-22') 
AND   r.status='1' 
ORDER BY r.id desc

Table reservation contains 500,000 records and bx_date varies from 2012-01-01 to 2013-01-01. 40,000 records in users table. bx_date is 'date' type.

With the query above if I tried a date like 2012-08-22, It takes 8 secs. But if I tried 2013-01-01 It takes like 1 sec.

What is the reason?

Answer 1

Data is available in your table reservation upto today('2012-08-22') . But, no data to be found for 2013. Data availability makes this change.