fetching a row in the middle of the table

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

The most likely cause is improper indexing or index maintenance.

You should have an index such as:

CREATE INDEX reservation_ndx ON reservation
    (status, bx_date, user_id)

Also, if using InnoDB, try running

ANALYZE TABLE reservation;

in order to let the system update the table demographics.

Another possible (but IMHO not so likely) cause could be table partitioning:

 http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html

seeing as reservation is probably filled incrementally with time.

update: I hadn’t mentioned the obvious cause, that one query might return one million records and the other query fail instantly, but if you don’t know what the SQL results are (maybe you’re just running ‘blind’ benchmarks and only check execution time), that also might be a possibility. (update on update: I just read that both queries return the same number of rows. This cause is then excluded)