在LIMDER BY之前或之后应用MySQL LIMIT吗？

Which one comes first when MySQL processes the query?

当MySQL处理查询时，哪一个首先出现？

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?

即使记录与ORDER BY后应用的修订日期时间IF LIMIT不匹配，是否会返回上次发布的pageRegion？

3 个解决方案

#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.

是的，它是在ORDER BY之后。对于您的查询，您将获得具有最高publishOn的记录，因为您正在订购DESC，在结果集中首先设置最大值，其中您选择第一个。

#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.

因此，您将获得与WHERE子句中的所有条件匹配的最接近的记录<= publishedOn。

#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.

只是想指出在限制结果之前应用MySQL排序的情况。但对于其他数据库而言，情况并非如此。

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

例如，Oracle首先限制结果并对所述结果应用排序。从性能的角度考虑时，这是有道理的。在MySQL中，您实际上是在订购整个数据库（> 1000条记录）以获得2

#1