Slow UNION Query - MySQL

Question

I have a UNION query consisting of two fast queries.

( SELECT DISTINCT ( SELECT strStatus FROM User_User_XR uuxr WHERE 
( uuxr.intUserId1 = '1' AND uuxr.intUserId2 = u.intUserId ) ) AS strFriendStatus1,
 uuxro.strStatus AS strFriendStatus2, uuxr.intUserId2 AS intUserId, u.strUserName ,
 u.strGender, IF( u.dtmBirth != '0000-00-00', FLOOR(DATEDIFF(CURDATE(), 
u.dtmBirth) / 365.25) , '?') AS intAge, u.strCountry AS strCountryCode,
 c.strCountry AS strCountry, u.strAvatar, u.fltPoints, 
IF( o.intUserId IS NULL, 'offline', 'online' ) AS strOnline, 
IF ( u.strAvatar != '', CONCAT( 'avatars/60/', u.strAvatar ), 
CONCAT( 'images/avatar_', u.strGender, '_small.png' ) ) as strAvatar,     
IF ( u.strAvatar != '', CONCAT( 'avatars/150/', u.strAvatar ),     
CONCAT( 'images/avatar_', u.strGender, '.png' )) as strLargeAvatar,
 u.dtmLastLogin, u.dtmRegistered FROM User_User_XR uuxr, 
User u LEFT JOIN User_User_XR uuxro ON uuxro.intUserId2 = '1' 
AND uuxro.intUserId1 = u.intUserId
 LEFT JOIN Online o ON o.intUserId = u.intUserId 
LEFT JOIN Country c ON c.strCountryCode = u.strCountry 
WHERE u.intUserId = uuxr.intUserId2 AND ( uuxr.strStatus = 'confirmed' ) 
AND uuxr.intUserId1='1' ) 

UNION 

( SELECT DISTINCT ( SELECT strStatus FROM User_User_XR uuxr 
WHERE ( uuxr.intUserId1 = '1' AND uuxr.intUserId2 = u.intUserId ) ) AS strFriendStatus1,
 uuxro.strStatus AS strFriendStatus2, uuxr.intUserId1 AS intUserId, u.strUserName , 
u.strGender, IF( u.dtmBirth != '0000-00-00', FLOOR(DATEDIFF(CURDATE(),
 u.dtmBirth) / 365.25) , '?') AS intAge,
 u.strCountry AS strCountryCode, c.strCountry AS strCountry, u.strAvatar, u.fltPoints, 
IF( o.intUserId IS NULL, 'offline', 'online' ) AS strOnline, 
IF ( u.strAvatar != '', CONCAT( 'avatars/60/', u.strAvatar ), 
CONCAT( 'images/avatar_', u.strGender, '_small.png' ) ) as strAvatar,
 IF ( u.strAvatar != '', CONCAT( 'avatars/150/', u.strAvatar ), 
CONCAT( 'images/avatar_', u.strGender, '.png' )) as strLargeAvatar, 
u.dtmLastLogin, u.dtmRegistered FROM User_User_XR uuxr, User u 
LEFT JOIN User_User_XR uuxro ON uuxro.intUserId2 = '1' 
AND uuxro.intUserId1 = u.intUserId 
LEFT JOIN Online o ON o.intUserId = u.intUserId 
LEFT JOIN Country c ON c.strCountryCode = u.strCountry 
WHERE u.intUserId = uuxr.intUserId1 AND ( uuxr.strStatus = 'confirmed' )
 AND uuxr.intUserId2='1' )

First of the queries runs in 0.0047s Second runs in 0.0043s

However, WITH the Union, they run 0.27s ... why is this? There is no Order By after the UNION, why wouldn't MySQL simply take the two fast queries and concatenate them?

Answer 1

A UNION causes a temporary table to be created, even for a UNION ALL.

When a UNION DISTINCT (which is the same as UNION) is performed, the temporary table is created with an index so that duplicates can be removed. With UNION ALL, the temporary table is created, but without the index.

This explains the slight performance improvement when using UNION ALL, and also accounts for the performance drop when using UNION instead of two separate queries.

For more information on this, see the following entry on the MySQL performance blog:

UNION vs UNION ALL Performance

The How MySQL Uses Internal Temporary Tables page from the MySQL docs states that a temporary table is created when:

... any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used

Answer 2

Try using UNION ALL.

UNION on its own will remove any duplicate records, which implies a behind-the-scenes sort.