My reading of the docs in https://www.sqlite.org/draft/tempfiles.html and in particular section 2.8 tells me that: If I do a DISTINCT, GROUP BY (for DISTINCT ON) or ORDER BY and I combine it with a LIMIT SQLite will still sort all the rows using a transient index and then apply the limit to the sorted rows. In principle you could do better by keeping an index of at most k distinct min values where k is the LIMIT amount (OFFSET complicates this as usual). Maybe a heap. Have I read that correctly? i.e. using LIMIT with ORDER BY doesn't help that much. I wasn't really expecting any sort of advanced "heap streaming" or anything like that but I was trying to cite the docs for clarity in a talk I'm giving. OTOH it might be fun to try to add such a thing... There are lots of cases where getting the "most recent" 'k' rows comes up and it would be cool to be able to do that more cheaply and then join from there to get your additional data. Anyone already looking into that?