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

Reply via email to