MySQL Forums
Forum List  »  Optimizer & Parser

Projection of a WHERE attribute
Posted by: brice chardin
Date: March 16, 2010 12:03PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Projection of a WHERE attribute
7075
March 16, 2010 12:03PM
1787
March 19, 2010 07:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.