CODEWITHSUNDEEP
sql
Andrew Eells
Andrew Eells

Reputation: 815

SQL query optimisation

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.

Upvotes: 2

Views: 99

Answers (1)

Costi Ciudatu
Costi Ciudatu

Reputation: 38195

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.

Upvotes: 2

Related Questions