0

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
  • May be UNION ALL if you don't expect duplicates? – mustaccio Feb 11 at 18:22
  • UNION = Remove duplicates = Sort combined recordset = Filesort = Slow. – Akina Feb 11 at 19:24
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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.