MySQL Forums
Forum List  »  Optimizer & Parser

Re: Select last row that is less than or equal to a date
Posted by: Rick James
Date: January 27, 2013 10:19AM

Numbers under 1000 are hardly worth optimizing. For your table there about 50 data rows in one disk block and about 80 index rows per block. Disk hits are (when not cached).

The Handler_write diff of 8 implies that it is creating a temp table. Yet, the EXPLAINs do not mention such. I can't explain.

I would expect
index PlayerDate ( HistoryPlayer, HistoryDate )
to be perfect for the LIMIT 0,1 query -- find the end of the HistoryPlayer=... (one _read_key), then fetch 1 (LIMIT 0,1) row via _read_prev. But it says 9 _read_next. Are there 8 rows for that HistoryPlayer? Or are there 59, as indicated by the query without the LIMIT?

Note: _read_next will have to overshoot by 1 to discover that it is through with the scan; hence the 8 versus 9.

MyISAM index blocks are cached in the key_buffer (of size key_buffer_size, which normally should be set to 20% of available RAM). Data blocks are cached by the OS. "Count the disk hits"; then assume about 100 hits per second. For more Rules of Thumb, see
http://mysql.rjweb.org/doc.php/ricksrots

I assume you did not run any other queries between the Handler queries and the SELECT?

In the EXPLAINs, the Date= case shows "ref" and "const,const". The Date< shows "range".

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Select last row that is less than or equal to a date
1669
January 27, 2013 10:19AM


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.