On 2015-04-08 01:57 PM, Dominique Devienne wrote: >> No Dominique, it's not that - >> > Perhaps. But that still doesn't get to my point. With a LIMIT clause, in > such a GROUP BY ORDER BY returning a large result set, would SQLite: > 1) sort the whole result-set and then keep only the first top-N rows? > 2) or instead do a partial-sort of the first top-N rows only, as in > http://www.cplusplus.com/reference/algorithm/partial_sort/? > > I'm interested in finding out for sure. Perhaps that's highjacking this > thread a bit, but in case of SQLite doing #1, and MySQL doing #2, it could > explain some of the difference. (although sorting a 1M array is so fast > nowadays, I doubt it.).
I think the partial sort algorithm only finds the first N items and then stops sorting, but for that to be possible the result set must be present in full and finished calculating in full already. The partial sort itself might save a millisecond or two from a complete sort in large lists. Either way, SQLite is more clever than that as Richard pointed out. > His rows are "fatter", since he mentioned 41 columns. Which might make it > go over some threshold(s) (page cache?) slowing things down once past it. > > But indeed, sharing the DB (if not sensitive data) would be the way to go. No no, we know his rows' fatness exactly, he did send the schema, they are 41 integer values, i.e it doesn't matter. So yeah, there must be some trivial thing which the OP (and I) are missing. Even an obscured values DB that still causes the slow query will work...
