Optimizing SELECT Statements method reads all keys in each range defined by the  WHERE  clause, or scans the whole index if there are no range conditions, it is called a  Tight Index Scan . With a Tight Index Scan, the grouping operation is performed only after all keys that satisfy the range conditions have been found. For this method to work, it is sufficient that there be a constant equality condition for all columns in a query referring to parts of the key coming before or in between parts of the  GROUP BY  key. The constants from the equality conditions fill in any “gaps”  in the search keys so that it is possible to form complete prefixes of the index. These index prefixes then can be used for index lookups. If the  GROUP BY  result requires sorting, and it is possible to form search keys that are prefixes of the index, MySQL also avoids extra sorting operations because searching with prefixes in an ordered index already retrieves all the keys in order. Assume that there is an index  idx(c1,c2,c3)  on table  t1(c1,c2,c3,c4) . The following queries do not work with the Loose Index Scan access method described previously, but still work with the Tight Index Scan access method. •There is a gap in the  GROUP BY , but it is covered by the condition  c2 = ’a’ : •The  GROUP BY  does not begin with the first part of the key, but there is a condition that provides a constant for that part: 8.2.1.18 DISTINCT Optimization SELECT c1, c2, c3 FROM t1 WHERE c1 = ’a’ GROUP BY c2, c3; SELECT c1, c2, c3 FROM t1 WHERE c2 = ’a’ GROUP BY c1, c3; SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 >  const ; SELECT c1, c2, c3 FROM t1 WHERE c1 >  const  GROUP BY c1, c2, c3; SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a; 1686 If you need only a specified number of rows from a result set, use a  LIMIT  clause in the query, rather than fetching the whole result set and throwing away the extra data. If you do not use columns from all tables named in a query, MySQL stops scanning any unused tables as soon as it finds the first match. In the following case, assuming that  t1  is used before  t2  (which you can check with  EXPLAIN ), MySQL stops reading from  t2  (for any particular row in  t1 ) when it finds the first row in  t2 : When combining  LIMIT  row_count  with  DISTINCT , MySQL stops as soon as it finds  row_count unique rows. Due to this equivalence, the optimizations applicable to  GROUP BY  queries can be also applied to queries with a  DISTINCT  clause. Thus, for more details on the optimization possibilities for  DISTINCT  queries, see Section 8.2.1.17, “GROUP BY Optimization” . In most cases, a  DISTINCT  clause can be considered as a special case of  GROUP BY . For example, the following two queries are equivalent: Because  DISTINCT  may use  GROUP BY , learn how MySQL works with columns in  ORDER BY  or  HAVING clauses that are not part of the selected columns. See Section 12.20.3, “MySQL Handling of GROUP BY” . DISTINCT  combined with  ORDER BY  needs a temporary table in many cases. 8.2.1.19 LIMIT Query Optimization