7.2.5 GROUP BY and Related Conditions

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 the ORDER BY is unnecessary, because the GROUP BY comes out in order by x.

• The optimizer contains code for shifting certain HAVING conditions to the WHERE 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 for InnoDB tables. Note that the query

  SELECT COUNT(column1) FROM Table1;

  is not subject to the same optimization, unless column1 is defined as NOT NULL.

• New optimizations exist for MAX() and MIN(). For example, consider the query

  SELECT MAX(column1)
    FROM Table1
    WHERE column1 < 'a';

  If column1 is indexed, then it's easy to find the highest value by looking for '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 the FROM clause, and no WHERE clause.)

  • There is no LIMIT clause.

  Because DISTINCT is not always transformed to GROUP BY, do not expect that queries with DISTINCT will always cause ordered result sets. (You can, however, rely on that rule with GROUP BY, unless the query includes ORDER BY NULL.)

See: /sql/sql_select.cc, opt_sum_query(), and /sql/sql_select.cc, remove_duplicates().