- 7.2 Optimizing SELECT and Other Statements
- 7.2.1 Optimizing Queries with EXPLAIN
- 7.2.2 Estimating Query Performance
- 7.2.3 Speed of SELECT Queries
- 7.2.4 WHERE Clause Optimization
- 7.2.5 Range Optimization
- 7.2.6 Index Merge Optimization
- 7.2.7 Condition Pushdown Optimization
- 7.2.8 Index Condition Pushdown Optimization
- 7.2.9 IS NULL Optimization
- 7.2.10 LEFT JOIN and RIGHT JOIN Optimization
- 7.2.11 Nested-Loop Join Algorithms
- 7.2.12 Nested Join Optimization
- 7.2.13 Outer Join Simplification
- 7.2.14 ORDER BY Optimization
- 7.2.15 GROUP BY Optimization
- 7.2.16 DISTINCT Optimization
- 7.2.17 Optimizing IN/=ANY Subqueries
- 7.2.18 LIMIT Optimization
- 7.2.19 Using optimizer_switch to Control the Optimizer
- 7.2.20 How to Avoid Table Scans
- 7.2.21 INFORMATION_SCHEMA Optimization
- 7.2.22 Speed of INSERT Statements
- 7.2.23 Speed of UPDATE Statements
- 7.2.24 Speed of DELETE Statements
- 7.2.25 Other Optimization Tips
DISTINCT combined with ORDER
BY needs a temporary table in many cases.
Because DISTINCT may use GROUP
BY, you should be aware of how MySQL works with
columns in ORDER BY or
HAVING clauses that are not part of the
selected columns. See Section 11.12.3, “GROUP BY and HAVING with Hidden
Columns”.
In most cases, a DISTINCT clause can be
considered as a special case of GROUP BY. For
example, the following two queries are equivalent:
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 >const; SELECT c1, c2, c3 FROM t1 WHERE c1 >constGROUP BY c1, c2, c3;
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 7.2.15, “GROUP BY Optimization”.
When combining LIMIT
with
row_countDISTINCT, MySQL stops as soon as it finds
row_count unique rows.
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:
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
User Comments
I have had some trouble with DISTINCT and ORDER BY, for example I could not get:
SELECT DISTINCT id,city FROM clients WHERE city like "k%" ORDER BY id DESC;
to work, as in it would not be correctly ordered.
However, doing this seems to work:
SELECT DISTINCT id,city FROM (clients inner join clients AS c on clients.id=c.id) WHERE city like "k%" ORDER BY id DESC;
I think it has to do with the way distinct is optimized, and if a a field marked in the distinct clause is a primary key, it will ignore it because it is distinct already and then it will ignore the order by. Sure, it doesn't make sense to use DISTINCT if you know you have a primary key. But if you have code where only sometimes this happens, and you don't want to completely recode everything this may be of help to you.
-Mark
Select count(distinct somefield) from sometable is very slow in all versions of mysql.
The distinct part is equivalent to:
select somefield from sometable group by somefield
although the latter sometimes runs even faster than distinct. To get reasonable speed under mysql5, please use this equivalent which uses a subquery:
select count(*) from (select distinct somefield from sometable group by somefield) as somelabel;
This is quite speedy.
(I don't know why count distinct is so slow when distinct itself isn't that slow.)
Add your own comment.