CODEWITHSUNDEEP
mysqlgroup-bydistinctexplain
marcosh
marcosh

Reputation: 9008

Almost equal queries give different execution times

I have these two queries:

SELECT SQL_NO_CACHE DISTINCT(type) FROM actions LIMIT 0, 30;

and

SELECT SQL_NO_CACHE type FROM actions GROUP BY type LIMIT 0, 30;

If I don't use the LIMIT clause, the execution times are equal. On the other hand, in my case, the first query takes almost 0.8 seconds, while the second takes 0.12 seconds.

Using EXPLAIN, it seems that the only difference is that first query uses a temporary table, while the second does not.

At this point I'm quite astonished by the different behavior of the two queries... can you provide some enlightenment on the problem?

I am currently using MYSQL 5.5.37-35.1 Percona Server (GPL), Release 35.1, Revision 666

Upvotes: 3

Views: 62

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53830

It seems that the LIMIT optimization is only properly applied with GROUP BY when there is an ORDER BY clause. As Gordon Linoff suggested in an earlier (deleted) answer, the GROUP BY query has an implicit ORDER BY. Therefore, the GROUP BY query uses the LIMIT optimization.

Even though, the DISTINCT query basically uses GROUP BY to solve it, the implicit ORDER BY is not there. Adding an explicit ORDER BY clause to the DISTINCT query yields the same execution plan and the same performance as the GROUP BY query:

SELECT SQL_NO_CACHE DISTINCT(type) FROM actions ORDER BY type LIMIT 0, 30;

and

SELECT SQL_NO_CACHE type FROM actions GROUP BY type LIMIT 0, 30;

Upvotes: 2

Related Questions