Re: Select last row that is less than or equal to a date

[Øystein Grøvlen]

Hi David,

As Rick says, Handler_write indicates that query has created a temporary table, and I would guess that the read_rnd_next count comes from reading this temporary table (since it represents the number of records read by a sequential table scan).
To confirm whether a temporary table is involved you could check the status variable Created_tmp_tables.

IIRC, in MySQL 5.5 and earlier, for queries with ORDER BY, MySQL may sometimes decide during execution to use another plan than the one presented by EXPLAIN. This problem have, AFAIK, been fixed in MySQL 5.6 so that EXPLAIN will show the query plan that will actually be used. It would be interesting to know how your query behaves with the MySQL 5.6 Release Candidate.

The numbers of Handler_read_key and Handler_read_prev is as expected. With LIMIT 0,1, a single key look-up is performed. Without the LIMIT clause, the index will be scanned backwards as indicated by the Handler_read_prev count.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway