MySQL Forums
Forum List  »  Optimizer & Parser

Re: Select last row that is less than or equal to a date
Posted by: Øystein Grøvlen
Date: January 30, 2013 04:50AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Select last row that is less than or equal to a date
2254
January 30, 2013 04:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.