MySQL Forums
Forum List  »  Newbie

Re: Optimizing GROUP BY
Posted by: rihad rihad
Date: December 24, 2008 02:54AM

Quote

Please show your real query, not a bogus one
OK, it can't get any reallier than this:

There are exactly 1 million rows in this static example table, of which exactly 612 match uid=206.

mysql> show index from logs;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| logs | 1 | uid | 1 | uid | A | 4878 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

key was created by KEY `uid` (`uid`)


Now I'd like this query to forgo using a temporary table:

mysql> EXPLAIN SELECT SUM(uid) FROM logs WHERE uid='206' GROUP BY uid ORDER BY NULL;
+----+-------------+----------+------+---------------+------+---------+-------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+-------+------+-------------------------------------------+
| 1 | SIMPLE | logs | ref | uid | uid | 4 | const | 612 | Using where; Using index; Using temporary |
+----+-------------+----------+------+---------------+------+---------+-------+------+-------------------------------------------+
1 row in set (0.00 sec)


Simple as that. I'd be grateful if someone could shed some light of why this doesn't work despite the docs meantioning it should be fetching the data directly from the index;

Options: ReplyQuote


Subject
Written By
Posted
December 23, 2008 02:11AM
December 23, 2008 11:43AM
December 23, 2008 10:45PM
Re: Optimizing GROUP BY
December 24, 2008 02:54AM
December 24, 2008 03:05AM
December 24, 2008 05:20PM
December 24, 2008 04:13AM
December 24, 2008 05:27PM
December 24, 2008 12:25PM


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.