UNION instead of OR in MySQL

i have MySQL that is run on arrays of increasing size:

SELECT ... FROM tableA LEFT JOIN tableB ON tableA.id=tableB.aId WHERE tableA.lastModified>? OR tableB.lastModified>?

i remembered that the query optimizer will not be able to use existing indices for lastModified columns but.. the query execution time was acceptable, so i did not bother. recently much more data was added and query above, repeated quite often, caused a lot of cpu usage on the server. so i’ve rewritten the query into union of two queries:

SELECT ... FROM tableA LEFT JOIN tableB ON tableA.id=tableB.aId WHERE tableA.lastModified>
UNION
SELECT ... FROM tableA LEFT JOIN tableB ON tableA.id=tableB.aId WHERE tableB.lastModified>?

in this case existing indices are properly used.. finally! the result:

Leave a Reply

Your email address will not be published. Required fields are marked *

 

(Spamcheck Enabled)