On Fri, 3 Jul 2015 11:35:21 +0100 Rob Willett <rob.sqlite at robertwillett.com> wrote:
> It basically has taken no time to run 10,551 selects. This implies > that we had issues on the database before with either fragmentation > or indexes being poorly setup. Perhaps you said so and I missed it, but to me it's clear that almost all the improvement comes from using RAG_Idx1. Because it indexes the Text column, you're now doing a binary search instead of a linear scan. By changing the collation, you went from scanning every row to searching a binary tree. That took you from O(n) to O(log2 n). If each row in your 16 GB database is 100 bytes, allowing for overhead you might have 80 million rows? To satisfy your query, on average that would be an improvement from 40,000,000 I/O operations to 13. When something that used to take 122 seconds starts going a million times faster, it begins to look pretty much instantaneous. :-) --jkl
