Re: Projection of a WHERE attribute

[brice chardin]

Another example on the same topic :

EXPLAIN shows that in both cases, the primary key index is used.
The first query implies a full index scan, while the second query - with "FORCE INDEX (PRIMARY)" - uses a range scan on the same index (but with different key_len).

mysql> EXPLAIN SELECT Id, max(Date) FROM Values GROUP BY Id;
+----+-------------+--------+-------+---------------+---------+---------+------+-----------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows      | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+-----------+-------------+
|  1 | SIMPLE      | Values | index | NULL          | PRIMARY | 12      | NULL | 499971000 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+-----------+-------------+

mysql> EXPLAIN SELECT Id, max(Date) FROM Values FORCE INDEX (PRIMARY) GROUP BY Id;
+----+-------------+--------+-------+---------------+---------+---------+------+-----------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows      | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+-----------+--------------------------+
|  1 | SIMPLE      | Values | range | NULL          | PRIMARY | 4       | NULL | 499971001 | Using index for group-by |
+----+-------------+--------+-------+---------------+---------+---------+------+-----------+--------------------------+

mysql> SELECT Id, max(Date) FROM Values GROUP BY Id;
+----+---------------+
| Id | max(Date)     |
+----+---------------+
|  1 | 1302238691129 |
|  2 | 1302238690713 |
|  3 | 1302238691729 |
|        ...         |
+----+---------------+
30 rows in set (11 min 59.82 sec)

mysql> SELECT Id, max(Date) FROM Values FORCE INDEX (PRIMARY) GROUP BY Id;
+----+---------------+
| Id | max(Date)     |
+----+---------------+
|  1 | 1302238691129 |
|  2 | 1302238690713 |
|  3 | 1302238691729 |
|        ...         |
+----+---------------+
30 rows in set (0.34 sec)

Edited 1 time(s). Last edit at 03/18/2010 04:28AM by brice chardin.