Almost equal queries give different execution times

Question

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

I presume that the 2nd query manages use utilise an index on type for the GROUP BY. — Kickstart, Jul 02 '14 at 13:44
coz `Distinct` means additional work to do, to neglect duplicate entries and thats why taking more time. — Vedant Terkar, Jul 02 '14 at 13:45
@VedantTerkar according to the mysql documentation DISTINCT should be a special case of a GROUP BY. And remember that without the LIMIT clause the queries perform the same way — marcosh, Jul 02 '14 at 13:50
@MarcusAdams I am using SQL_NO_CACHE (I edited the question). type is a varchar — marcosh, Jul 02 '14 at 13:53
@VedantTerkar . . . One query uses `distinct` and the other uses `group by`. These two queries should be doing the same thing. — Gordon Linoff, Jul 02 '14 at 13:55
GROUP BY allows you to group by more than one column so I guess that's the reason it's slower — Samuil Banti, Jul 02 '14 at 14:15
What is the length of the `type` VARCHAR column, also what character set, and what storage engine? — Marcus Adams, Jul 02 '14 at 14:17
@MarcusAdams length is 64, character set is utf8_general_ci, storage engine is MyISAM — marcosh, Jul 02 '14 at 14:27

score 2 · Accepted Answer · answered Jul 02 '14 at 14:38

2

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;

answered Jul 02 '14 at 14:38

Marcus Adams

53,009
9
91
143

I absolutely agree. (And I wonder why the answer was deleted :).) But I was wondering why DISTINCT doesn't also implicitly use an ORDER BY when there's an index that would make it fast. And moreover, I tried similar queries to the ones in the question and the end result is in the same order - do you think this is to be expected for some reason? Thanks – nicolagi Jul 02 '14 at 14:44
@idrarig, I guess it's the implicit or explicit `ORDER BY` that triggers the `LIMIT` optimization, regardless of what else is going on. Room for improvement to be sure. – Marcus Adams Jul 02 '14 at 15:00

Almost equal queries give different execution times

1 Answers1