These are the main optimizations that take place for
GROUP BY
and related items
(HAVING
, COUNT()
,
MAX()
, MIN()
,
SUM()
, AVG()
,
DISTINCT()
).
GROUP BY
will use an index, if one exists.GROUP BY
will use sorting, if there is no index. The optimizer may choose to use a hash table.For the case
GROUP BY x ORDER BY x
, the optimizer will realize that theORDER BY
is unnecessary, because theGROUP BY
comes out in order byx
.The optimizer contains code for shifting certain
HAVING
conditions to theWHERE
clause; however, this code is not operative at time of writing. See:/sql/sql_select.cc
,JOIN::optimize()
, after#ifdef HAVE_REF_TO_FIELDS
.-
If the table handler has a quick row-count available, then the query
SELECT COUNT(*) FROM Table1;
gets the count without going through all the rows. This is true for
MyISAM
tables, but not forInnoDB
tables. Note that the querySELECT COUNT(column1) FROM Table1;
is not subject to the same optimization, unless
column1
is defined asNOT NULL
. -
New optimizations exist for
MAX()
andMIN()
. For example, consider the querySELECT MAX(column1) FROM Table1 WHERE column1 < 'a';
If
is indexed, then it's easy to find the highest value by looking forcolumn1
'a'
in the index and going back to the key before that. -
The optimizer transforms queries of the form
SELECT DISTINCT column1 FROM Table1;
to
SELECT column1 FROM Table1 GROUP BY column1;
if and only if both of these conditions are true:
The
GROUP BY
can be done with an index. (This implies that there is only one table in theFROM
clause, and noWHERE
clause.)There is no
LIMIT
clause.
Because
DISTINCT
is not always transformed toGROUP BY
, do not expect that queries withDISTINCT
will always cause ordered result sets. (You can, however, rely on that rule withGROUP BY
, unless the query includesORDER BY NULL
.)
See:
/sql/sql_select.cc
,
opt_sum_query()
, and
/sql/sql_select.cc
,
remove_duplicates()
.