SQL query optimisation

Question

I'd like to optimise the following statements, preferably into a single query if possible:

SELECT * FROM a WHERE status = 'x' AND a_date < NOW() ORDER BY a_date ASC LIMIT 1; SELECT * FROM a WHERE status = 'y' ORDER BY a_date ASC LIMIT 1;

where the second statement should only be evaluated if the first returns NULL.

I have indexing set up correctly, this is more a question of can I issue a single statement instead of issuing a secondary statement after an application logic check for the null object.

Thanks for any input.

Answer 1

Try this:

SELECT * FROM a WHERE (status = 'x' AND a_date < NOW()) OR (status = 'y')
ORDER BY status ASC, a_date ASC LIMIT 1;

Here you rely on the fact that status 'x' will come before status 'y' in the natural order (if it's the other way around, change ASC with DESC in ORDER BY status.