Hi,
I have a table storing time series values (Id, Date, Value, Quality).
When I want to select the last Value for a given Id, I see weird optimizer choices depending on the query :
"SELECT max(Date) FROM Values WHERE Id = 1" (0.00sec) is a lot faster than "SELECT Id, max(Date) FROM Values WHERE Id = 1" (8.94sec).
Actually, I am not interested in optimizing this particular query, just curious about where these optimizer choices are coming from ; if anyone has an explanation.
Regards,
mysql> SHOW CREATE TABLE Values;
+--------+-------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------+
| Values | CREATE TABLE `Values` |
| | ( |
| | `Id` int(11) NOT NULL, |
| | `Date` bigint(20) unsigned NOT NULL, |
| | `Value` tinyint(1) DEFAULT NULL, |
| | `Quality` tinyint(3) unsigned NOT NULL, |
| | PRIMARY KEY (`Id`,`Date`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------+
mysql> SHOW INDEX FROM Values;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Values | 0 | PRIMARY | 1 | Id | A | 499971579 | NULL | NULL | | BTREE | |
| Values | 0 | PRIMARY | 2 | Date | A | 499971579 | NULL | NULL | | BTREE | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> EXPLAIN SELECT Id, max(Date) FROM Values WHERE Id = 1;
+----+-------------+--------+------+---------------+---------+---------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+----------+-------------+
| 1 | SIMPLE | Values | ref | PRIMARY | PRIMARY | 4 | const | 11801910 | Using index |
+----+-------------+--------+------+---------------+---------+---------+-------+----------+-------------+
mysql> EXPLAIN SELECT max(Date) FROM Values WHERE Id = 1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
mysql> SELECT max(Date) FROM Values WHERE Id = 1;
+---------------+
| max(Date) |
+---------------+
| 1302238691129 |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT Id, max(Date) FROM Values WHERE Id = 1;
+----+---------------+
| Id | max(Date) |
+----+---------------+
| 1 | 1302238691129 |
+----+---------------+
1 row in set (8.94 sec)
Using MySQL 5.1.39 on Ubuntu 9.04 Server