MySQL Forums
Forum List  »  Optimizer & Parser

Re: Projection of a WHERE attribute
Posted by: brice chardin
Date: March 18, 2010 04:15AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
7076
March 16, 2010 12:03PM
Re: Projection of a WHERE attribute
2167
March 18, 2010 04:15AM
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.