MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query with GROUP BY (Using index for group-by -> Using index)
Posted by: mnf
Date: December 25, 2006 06:35AM

Much worse:

mysql> explain SELECT MAX(i) AS maxi FROM shn WHERE i <= 1000000000 GROUP BY n;
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | shn | range | PRIMARY | ni | 6 | NULL | 217 | Using where; Using index for group-by |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT group_concat(maxi) FROM (SELECT MAX(i) AS maxi FROM shn WHERE i <= 10000000 GROUP BY n) as T1;
+----+-------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 216 | |
| 2 | DERIVED | shn | index | PRIMARY | ni | 6 | NULL | 7699628 | Using where; Using index |
+----+-------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
2 rows in set (8.18 sec)

mysql> SELECT MAX(i) AS maxi FROM shn WHERE i <= 1000000000 GROUP BY n;
...
216 rows in set (0.00 sec)

mysql> SELECT group_concat(maxi) FROM (SELECT MAX(i) AS maxi FROM shn WHERE i <= 10000000 GROUP BY n) as T1;
...
1 row in set, 1 warning (8.05 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query with GROUP BY (Using index for group-by -> Using index)
3208
December 25, 2006 06:35AM


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.