Slow execution with MariaDB

Question

Not sure if this can be done without UNION. I think it is the UNION which makes it slow. The two parts are quite fast on their own, but combined with the UNION this code takes> 5 seconds to complete. Can it be optimized?

SELECT rl.Updated, c.CultureName, 
"CollectionName" 
as CollectionName, k.KeyName , rl.Value , s.StatusName ,
rl.Comment , rl.Comment as ActionInfo, a.ActionName, u.UserName 
FROM ResourceLog rl 
INNER JOIN `Status` s ON rl.StatusId = s.StatusId 
INNER JOIN Culture c ON rl.CultureId = c.CultureId 
INNER JOIN `Key` k ON rl.KeyId = k.KeyId 
LEFT JOIN `Action` a ON rl.ActionId = a.ActionId 
LEFT JOIN `User` u ON rl.UserId = u.UserId 
UNION SELECT imp.Created as Updated, "Imported" as CultureName, 
"None" as CollectionName, "Imported" as KeyName, 
"Imported" as Value, "Imported" as StatusName, 
"Imported" as Comment, "ActionInfo" as ActionInfo, 
"Imported" as ActionName, UserId 
FROM Import imp 
ORDER BY Updated DESC, KeyName, CultureName limit 50

• mustaccio: May be UNION ALL if you don't expect duplicates?
• Akina: UNION = Remove duplicates = Sort combined recordset = Filesort = Slow.

Answer 1

I suspect the combined query is not doing what you think it is doing.

Add parentheses to make it clear whether the second ORDER BY belongs to the second SELECT or to the UNION. Ditto for the LIMIT.

( SELECT ...
)
UNION
( SELECT ... 
 -- Do the ORDER BY and LIMIT belong here?
) 
-- Or here (for the UNION)?

For further discussion, Please provide

EXPLAIN ...
SHOW CREATE TABLE ...
how big are the tables