Re: Query execution plan

[Chris Slominski]

A modified query seems to solve the problem. In this example 3:42.11 goes to 0.00 using what would appear to be a significantly more complex query.

mysql> select * from table_38740 where time <= 5706114878826086400 order by time desc limit 1;
+---------------------+------+--------+
| time | code | val1 |
+---------------------+------+--------+
| 5706114875966008424 | 0 | -179.1 |
+---------------------+------+--------+
1 row in set (3 min 42.11 sec)

mysql> explain select * from table_38740 where time <= 5706114878826086400 order by time desc limit 1;
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | table_38740 | index | PRIMARY | PRIMARY | 8 | NULL | 1 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

--------------- Better ------------------

mysql> select * from table_38740 where time = (select max(time) from table_38740 where time <= 5706114878826086400);
+---------------------+------+--------+
| time | code | val1 |
+---------------------+------+--------+
| 5706114875966008424 | 0 | -179.1 |
+---------------------+------+--------+
1 row in set (0.00 sec)

mysql> explain select * from table_38740 where time = (select max(time) from table_38740 where time <= 5706114878826086400);
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+------------------------------+
| 1 | PRIMARY | table_38740 | const | PRIMARY | PRIMARY | 8 | const | 1 | |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+------------------------------+
2 rows in set (0.00 sec)