SQLite 似乎没有应用内部选择的限制
下面的 SQLite 查询很快（使用 sqlite 3.7.5）：
sqlite> select distinct DOB from (select * from MyTable limit 3) limit 20;
1958-11-05
1959-01-01
1963-06-07
而下面的查询很慢（limit 20 被删除）：
sqlite> select distinct DOB from (select * from MyTable limit 3);
1933-01-03
1934-12-24
1935-01-07
我发现这令人惊讶，因为我期望内部 limit 快速仅提取 3 行，因此外部 limit 20 确实无关紧要；然而，两个查询的时间却截然不同。为什么第一个查询要快得多？
The following SQLite query is fast (with sqlite 3.7.5):
sqlite> select distinct DOB from (select * from MyTable limit 3) limit 20;
1958-11-05
1959-01-01
1963-06-07
while the following one is slow (limit 20 was removed):
sqlite> select distinct DOB from (select * from MyTable limit 3);
1933-01-03
1934-12-24
1935-01-07
I find this surprising, because I expect the inner limit to quickly extract only 3 rows, so that the external limit 20 really does not matter; however, the timings of both queries are quite different. Why is the first query much faster?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
当外部查询涉及 DISTINCT 时，SQLite 在折叠子查询方面存在一些错误。 LIMIT 被迁移到外部查询，然后需要解析 DISTINCT，然后才能生成 3 条不完全相同的记录（由于不同）。
相关：SQLite 外部查询返回内部查询中未找到的结果
解决方法（使用 OFFSET 避免折叠）可能适用于你的场景也是如此。
SQLite has some bugs around folding subqueries when the outer query involves DISTINCT. The LIMIT is migrated to the outer query, which then require DISTINCT to be resolved before it can produce 3 records that are not all the same (due to distinct).
Related: SQLite outer query is returning results not found in inner query
The workaround (using OFFSET to avoid the folding) could work for your scenario as well.