StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTwo queries faster than than one?
text
Body
copied!<p>I have a table with columns:</p> <pre><code>CREATE TABLE aggregates ( a VARHCAR, b VARCHAR, c VARCHAR, metric INT KEY test (a, b, c, metric) ); </code></pre> <p>If I do a query like:</p> <pre><code>SELECT b, c, SUM(metric) metric FROM aggregates WHERE a IN ('a', 'couple', 'of', 'values') GROUP BY b, c ORDER BY b, c </code></pre> <p>The query takes 10 seconds, explain is:</p> <pre><code>+----+-------------+------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+ | 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 | +----+-------------+------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+ </code></pre> <p>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:</p> <pre><code>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 </code></pre> <p>The query takes 1 second and the explain is:</p> <pre><code>+----+-------------+------------+-------+---------------+------+---------+------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+------+---------+------+--------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 252 | Using temporary; Using filesort | | 2 | DERIVED | aggregates | range | test | test | 767 | NULL | 582383 | Using where; Using index | +----+-------------+------------+-------+---------------+------+---------+------+--------+---------------------------------+ </code></pre> <p>Why is this? Why is it faster if I do the grouping in a separate, outer query, instead of it just doing it all in one?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload