Optimizing SELECT Statements EXPLAIN  does not distinguish whether the optimizer does or does not perform a  filesort in memory. Use of an in-memory  filesort  can be seen in optimizer trace output. Look for filesort_priority_queue_optimization . For information about the optimizer trace, see MySQL Internals: Tracing the Optimizer. The most general way to satisfy a  GROUP BY  clause is to scan the whole table and create a new temporary table where all rows from each group are consecutive, and then use this temporary table to discover groups and apply aggregate functions (if any). In some cases, MySQL is able to do much better than that and avoid creation of temporary tables by using index access. The most important preconditions for using indexes for  GROUP BY  are that all  GROUP BY  columns reference attributes from the same index, and that the index stores its keys in order (as is true, for example, for a  BTREE  index, but not for a  HASH  index). Whether use of temporary tables can be replaced by index access also depends on which parts of an index are used in a query, the conditions specified for these parts, and the selected aggregate functions. There are two ways to execute a  GROUP BY  query through index access, as detailed in the following sections. The first method applies the grouping operation together with all range predicates (if any). The second method first performs a range scan, and then groups the resulting tuples. Loose Index Scan can also be used in the absence of  GROUP BY  under some conditions. See Skip Scan Range Access Method. The most efficient way to process  GROUP BY  is when an index is used to directly retrieve the grouping columns. With this access method, MySQL uses the property of some index types that the keys are ordered (for example,  BTREE ). This property enables use of lookup groups in an index without having to consider all keys in the index that satisfy all  WHERE  conditions. This access method considers only a fraction of the keys in an index, so it is called a  Loose Index Scan . When there is no  WHERE  clause, a Loose Index Scan reads as many keys as the number of groups, which may be a much smaller number than that of all keys. If the  WHERE  clause contains range predicates (see the discussion of the  range  join type in Section 8.8.1, “Optimizing Queries with EXPLAIN” ), a Loose Index Scan looks up the first key of each group that satisfies the range conditions, and again reads the smallest possible number of keys. This is possible under the following conditions: 1684 •Any other parts of the index than those from the  GROUP BY  referenced in the query must be constants (that is, they must be referenced in equalities with constants), except for the argument of  MIN()  or MAX()  functions. •The only aggregate functions used in the select list (if any) are  MIN()  and  MAX() , and all of them refer to the same column. The column must be in the index and must immediately follow the columns in the GROUP BY . •The  GROUP BY  names only columns that form a leftmost prefix of the index and no other columns. (If, instead of  GROUP BY , the query has a  DISTINCT  clause, all distinct attributes refer to columns that form a leftmost prefix of the index.) For example, if a table  t1  has an index on  (c1,c2,c3) , Loose Index Scan is applicable if the query has  GROUP BY c1, c2 . It is not applicable if the query has  GROUP BY c2, c3  (the columns are not a leftmost prefix) or  GROUP BY c1, c2, c4  ( c4  is not in the index). •The query is over a single table. •Tight Index Scan •Loose Index Scan Loose Index Scan 8.2.1.17 GROUP BY Optimization