Slow UNION query - MySQL

Question

I have a UNION query consisting of two quick 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' ) 

The first query runs at 0.0047s The second run at 0.0043s

However, with the Union, they launch 0.27s ... why is this? After UNION there is no Order By Why, why shouldn't MySQL just take two quick queries and concatenate them?

Answer 1

A UNION leads to the creation of a temporary table, even for UNION ALL .

When UNION DISTINCT is executed (the same as UNION ), a temporary table is created with an index so that duplicates can be deleted. Using UNION ALL , a temporary table is created, but without an index.

This explains a slight performance improvement when using UNION ALL , and also takes into account the performance degradation when using UNION instead of two separate queries.

For more information about this, see the following entry in the MySQL Performance Blog :

UNION vs UNION ALL Performance

How MySQL Uses Internal Temporary Tables from Documents MySQL 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 by itself will remove any duplicate entries, which means sorting by frame.