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

[Rick James]

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".