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

[David Marcus]

It seems it was the MySQL Query Browser. Here is what I get with mysql.exe:

mysql> show session status where Variable_name like 'Handler%' or Variable_name = 'Created_tmp_tables';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Created_tmp_tables | 0 |
| 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 | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
19 rows in set (0.00 sec)

mysql> select * from History where HistoryPlayer = 59161 and HistoryDate <= '2012-01-01' order by HistoryDate desc l
imit 0,1;
+--------------+---------------+-------------+-----------------+--------------------+---------------------+------------------+-------------------+
| HistoryEvent | HistoryPlayer | HistoryDate | HistoryReportID | HistoryInitialMean | HistoryInitialStDev | HistoryFinalMean | HistoryFinalStDev |
+--------------+---------------+-------------+-----------------+--------------------+---------------------+------------------+-------------------+
| 10868 | 59161 | 2011-12-25 | 59161 | 1419 | 59 | 1405 | 56 |
+--------------+---------------+-------------+-----------------+--------------------+---------------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show session status where Variable_name like 'Handler%' or Variable_name = 'Created_tmp_tables';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Created_tmp_tables | 0 |
| 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 | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
19 rows in set (0.00 sec)

mysql> 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 | NULL | 70 | 100.00 | Using index condition |
+----+-------------+---------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
----------------------------------------------------------------------