Hi everyone, I have copied the original database on my personnal website in a tbz archive here :
http://nice-waterpolo.com/misc/db/ There is only one index on timestamp,protocol. Thanks. 2015-04-08 14:38 GMT+02:00 R.Smith <rsmith at rsweb.co.za>: > > > 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... > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >