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

[David Marcus]

select version()
'5.6.9-rc'

show session status where Variable_name like 'Handler%' or Variable_name = 'Created_tmp_tables';
"Variable_name","Value"
"Created_tmp_tables","1"
"Handler_commit","0"
"Handler_delete","0"
"Handler_discover","0"
"Handler_external_lock","0"
"Handler_mrr_init","0"
"Handler_prepare","0"
"Handler_read_first","0"
"Handler_read_key","0"
"Handler_read_last","0"
"Handler_read_next","0"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","2"
"Handler_rollback","0"
"Handler_savepoint","0"
"Handler_savepoint_rollback","0"
"Handler_update","0"
"Handler_write","1"

select * from History where HistoryPlayer = 59161 and HistoryDate <= '2012-01-01' order by HistoryDate desc limit 0,1;
"HistoryEvent","HistoryPlayer","HistoryDate","HistoryReportID","HistoryInitialMean","HistoryInitialStDev","HistoryFinalMean","HistoryFinalStDev"
10868,59161,"2011-12-25",59161,1419,59,1405,56

show session status where Variable_name like 'Handler%' or Variable_name = 'Created_tmp_tables';
"Variable_name","Value"
"Created_tmp_tables","2"
"Handler_commit","0"
"Handler_delete","0"
"Handler_discover","0"
"Handler_external_lock","2"
"Handler_mrr_init","0"
"Handler_prepare","0"
"Handler_read_first","0"
"Handler_read_key","1"
"Handler_read_last","0"
"Handler_read_next","0"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","11"
"Handler_rollback","0"
"Handler_savepoint","0"
"Handler_savepoint_rollback","0"
"Handler_update","0"
"Handler_write","9"

explain extended select * from History where HistoryPlayer = 59161 and HistoryDate <= '2012-01-01' order by HistoryDate desc limit 0,1;
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","range","PlayerDate","PlayerDate","7","",73,100.00,"Using index condition"

5.5 said "Using where", but 5.6 says "Using index condition". It still
seems to be using a temporary table. I'm doing this on Windows Vista
32 bit.