Performance issues with ORed conditions
A classic problem case is this:
SELECT ... FROM foo JOIN bar ON ... WHERE foo.x = 1 OR bar.y = 2;
This performs poorly because the entire join result must be generated and then filtered according to the ORed conditions; there’s no way to use indexes on foo.x or bar.y to speed things up. The fix is to rewrite the query to use UNION instead of OR:
SELECT ... FROM foo JOIN bar ON ... WHERE foo.x = 1 UNION SELECT ... FROM foo JOIN bar ON ... WHERE bar.y = 2;