问题:

Which one comes first when MySQL processes the query?

An example:

SELECT pageRegions
FROM pageRegions WHERE(pageID=?) AND(published=true) AND (publishedOn<=?)
ORDER BY publishedON DESC
LIMIT 1';

Will that return the last published pageRegion even if the record does not match the revision datetime IF LIMIT is applied after ORDER BY?

回答1:

Yes, it's after the ORDER BY. For your query, you'd get the record with the highest publishedOn, since you're ordering DESC, making the largest value first in the result set, of which you pick out the first one.

回答2:

The limit is always applied at the end of result gathering, therefore after order by.

Given all your clauses, the order of processing will be

FROM
WHERE
SELECT
ORDER BY
LIMIT

So you will get the closest record <= publishedOn matching all the conditions in the WHERE clause.

回答3:

Just wanted to point out the in case of MySQL ordering is applied before limiting the results. But this is not true for other DB.

For example Oracle first limits results and applies ordering on said results. It makes sense when you think about it from a performance point of view. In MySQL you are actually ordering the entire DB(> 1000 records) to get 2