Optimizing SELECT Statements •For columns in the index, full column values must be indexed, not just a prefix. For example, with  c1 VARCHAR(20), INDEX (c1(10)) , the index uses only a prefix of  c1  values and cannot be used for Loose Index Scan. If Loose Index Scan is applicable to a query, the  EXPLAIN  output shows  Using index for group-by in the  Extra  column. Assume that there is an index  idx(c1,c2,c3)  on table  t1(c1,c2,c3,c4) . The Loose Index Scan access method can be used for the following queries: The following queries cannot be executed with this quick select method, for the reasons given: Were the query to include  WHERE c3 =  const , Loose Index Scan could be used. The Loose Index Scan access method can be applied to other forms of aggregate function references in the select list, in addition to the  MIN()  and  MAX()  references already supported: Assume that there is an index  idx(c1,c2,c3)  on table  t1(c1,c2,c3,c4) . The Loose Index Scan access method can be used for the following queries: A Tight Index Scan may be either a full index scan or a range index scan, depending on the query conditions. When the conditions for a Loose Index Scan are not met, it still may be possible to avoid creation of temporary tables for  GROUP BY  queries. If there are range conditions in the  WHERE  clause, this method reads only the keys that satisfy these conditions. Otherwise, it performs an index scan. Because this 1685 Tight Index Scan SELECT c1, c2 FROM t1 GROUP BY c1, c2; SELECT DISTINCT c1, c2 FROM t1; SELECT c1, MIN(c2) FROM t1 GROUP BY c1; SELECT c1, c2 FROM t1 WHERE c1 <  const  GROUP BY c1, c2; SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 >  const  GROUP BY c1, c2; SELECT c2 FROM t1 WHERE c1 <  const  GROUP BY c1, c2; SELECT c1, c2 FROM t1 WHERE c3 =  const  GROUP BY c1, c2; SELECT c1, SUM(c2) FROM t1 GROUP BY c1; SELECT c1, c2 FROM t1 GROUP BY c2, c3; SELECT c1, c3 FROM t1 GROUP BY c1, c2; •There are aggregate functions other than  MIN()  or  MAX() : •The columns in the  GROUP BY  clause do not form a leftmost prefix of the index: •The query refers to a part of a key that comes after the  GROUP BY  part, and for which there is no equality with a constant: • AVG(DISTINCT) ,  SUM(DISTINCT) , and  COUNT(DISTINCT)  are supported.  AVG(DISTINCT) and  SUM(DISTINCT)  take a single argument.  COUNT(DISTINCT)  can have more than one column argument. •There must be no  GROUP BY  or  DISTINCT  clause in the query. •The Loose Index Scan limitations described previously still apply. SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1; SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;