Re: Optimizing GROUP BY

[rihad rihad]

Here's another one:

Quote

explain SELECT class, SUM(in) FROM logs WHERE uid=206 AND start>='2008-12-16' GROUP BY class, uid ORDER BY NULL;
+----+-------------+----------+-------+---------------+-----------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+-----------+---------+------+------+------------------------------+
| 1 | SIMPLE | logs | range | uid_start | uid_start | 12 | NULL | 1 | Using where; Using temporary |
+----+-------------+----------+-------+---------------+-----------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

How can I optimize the query to use indexes for GROUP BY and not use temporary tables?

Its schema:

Quote

CREATE TABLE `logs` (
`uid` int(11) unsigned NOT NULL DEFAULT '0',
`start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`class` smallint(6) unsigned NOT NULL DEFAULT '0',
`in` int(11) unsigned NOT NULL DEFAULT '0',
KEY `uid_start` (`uid`,`start`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;