CODEWITHSUNDEEP
sqliteperformancelimit
Eric O. Lebigot
Eric O. Lebigot

Reputation: 94515

Limit on inner select does not seem to be applied by SQLite

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?

Upvotes: 0

Views: 151

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

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.

Upvotes: 1

Related Questions