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: