On 2017/09/06 11:17 PM, Nico Williams wrote:
If you'll redo this I'd urge you to use WITHOUT ROWIDS. First, that's
almost always the right thing to do anyways. Second, it won't perform
worse but likely will perform better. Third, write performance
definitely should improve with WITHOUT ROWIDS. Fourth, I think users
are starting to use WITHOUT ROWIDS more, so testing that seems more
All good points which I will definitely heed. I will of course test both
types of tables but ensure that when using row_id tables I do not
inadvertently have possible PK short-circuiting of query plans.
It's still remarkable that in both tests 5 and 6 I've used the very same
PK setup, yet Test 6 was significantly faster with the added ORDER BY
clause. In tests 1 through 4 I did not use a PK at all, just plain INT
data field, but then I did not test the ORDER BY in those tests.
It might turn out to be a wild goose chase, but that will be easily
evident when testing without the PK and with more realistic real-world
data. I'll do that this weekend.
sqlite-users mailing list