On Sat, Sep 09, 2017 at 05:56:03PM +0200, R Smith wrote: > *Findings* pertaining to JOIN, INTERSECT and WHERE IN (...) type queries on > tables *WITHOUT Row_ids*: > (This is the full test posted below because it is the one that matters most) > INTERSECT AND WHERE IN (...) queries posted similar times here with WHERE IN > (...) being the slightly faster (similar to the above findings), but BOTH > were a good 10 - 15% faster than on the ROW-ID tables, so WITHOUT ROWID > tables seem to have a definite advantage here (it is currently unclear to me > why this is). > > A troubling test is the JOIN on WITHOUT ROWID tables - it took several > orders of magnitude longer than any other test in the entire experiment.
In your first test you were ordering by PK, now you're not, and you don't have an [covering] index on the columns you're ordering by, so, yeah, "orders of magnitude" slower is to be expected. You're comparing apples and oranges. Also, in your first test you have one column. Now you have lots. That's no way to isolate the performance numbers you're trying to get. The only way to have an ORDER BY speed up a query is when there's an index on the columns in question that can be scanned to produce the order you're looking for without additional sorting, or when the engine can create such an index as a temporary index (this, of course, has a cost, so it won't always work well) Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
