EXPLAIN EXTENDED

How to create fast database queries

Archive for May 16th, 2009

UNION vs UNION ALL

Comments enabled. I *really* need your comment

In my previous article:

I described efficient ORDER BY along with LIMIT for UNION'ed resultsets.

One thing left unanswered: why UNION DISTINCT in some cases is faster than UNION ALL?

UNION DISTINCT (for which a mere UNION is an alias) implies selecting DISTINCT values from the resultsets, while UNION ALL just selects all values, including duplicates.

On one hand, UNION ALL does not perform duplicate checking, which should be faster (and almost always is). On the other hand, it returns more rows.

If some complex calculations are performed on these rows, this may kill all performance benefits.

Let's create the sample tables:
Read the rest of this entry »

Written by Quassnoi

May 16th, 2009 at 11:00 pm

Posted in MySQL