Combining Select Distinct With Union Distinct In Mysql - Any Effect?

The following two SQL statements are functionally identical: SELECT DISTINCT a,b,c FROM table1 UNION DISTINCT SELECT DISTINCT a,b,c FROM table2 and SELECT a,b,c FROM table1 UNION

Solution 1:

You need to check the execution plans. However, I would expect that the execution plans are different -- or at least they should be in some circumstances.

The first query:

SELECTDISTINCT a, b, c FROM table1
UNIONDISTINCTSELECTDISTINCT a, b, c FROM table2

can readily take advantage of indexes on table1(a, b, c) and table2(a, b, c)before doing the final UNION. This should speed the final union by reducing the size of the data. The second query doesn't have this advantage.

In fact, the most efficient way to write this query would probably be to have the two indexes and use:

SELECTDISTINCT a, b, c FROM table1 t1
UNIONALLSELECTDISTINCT a, b, c
FROM table2 t2
WHERENOTEXISTS (SELECT1FROM table1 t1 WHERE t2.a = t1.a and t2.b = t1.b and t2.c = t1.c)

This is almost identical, although it might handle NULL values in the second table a bit differently.