Edit Question - KnowledgeHub

Title Body

I have a table with columns: CREATE TABLE aggregates ( a VARHCAR, b VARCHAR, c VARCHAR, metric INT KEY test (a, b, c, metric) ); If I do a query like: SELECT b, c, SUM(metric) metric FROM aggregates WHERE a IN ('a', 'couple', 'of', 'values') GROUP BY b, c ORDER BY b, c The query takes 10 seconds, explain is: +----+-------------+------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+ | 1 | SIMPLE | aggregates | range | test | test | 767 | NULL | 582383 | Using where; Using index; Using temporary; Using filesort | +----+-------------+------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+ If I also group by/order by column a, so it doesn't need temporary/filesort, but then do the same thing in another query myself: SELECT b, c, SUM(metric) metric FROM ( SELECT a, b, c, SUM(metric) metric FROM aggregates WHERE a IN ('a', 'couple', 'of', 'values') GROUP BY a, b, c ORDER BY a, b, c ) t GROUP BY b, c ORDER BY b, c The query takes 1 second and the explain is: +----+-------------+------------+-------+---------------+------+---------+------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+------+---------+------+--------+---------------------------

Tags (comma-separated) Cancel