Skip to content

Instantly share code, notes, and snippets.

@ellisgl
Last active November 6, 2021 15:55
Show Gist options
  • Save ellisgl/b72173f85ec4e5ac21fe415914a2f361 to your computer and use it in GitHub Desktop.
Save ellisgl/b72173f85ec4e5ac21fe415914a2f361 to your computer and use it in GitHub Desktop.
MySQL performance optimization (OR to UNION)
# tbla idx1 b,e,f
SELECT a.*
FROM tblA a
INNER JOIN tblB b ON a.id = b.aId
WHERE a.b IN (1,2,3)
AND (a.c IN (3,4,5) OR a.d IS NOT NULL)
AND a.e = 0
AND a.f = 0
ORDER BY a.g ASC
# tbla idx2 b,c,e,f
# tbla idx3 b,d,e,f
SELECT a.*
FROM tblA a
INNER JOIN tblB b ON a.id = b.aId
WHERE a.id IN (
SELECT id
FROM (
SELECT id
FROM tblA a2
WHERE a2.b IN (1,2,3)
AND a2.c IN (3,4,5)
AND a2.e = 0
AND a2.f = 0
UNION ALL
SELECT id
FROM tblA a3
WHERE a3.b IN (1,2,3)
AND a3.d IS NOT NULL
AND a3.e = 0
AND a3.f = 0
UNION ALL
) subIds
)
ORDER BY a.g ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment