Posts tagged "sqlite"

When an index has every column a query needs, SQLite never opens the table. For one row this saves almost nothing. For 67,245 rows it was 98 ms against 21 ms with a warm cache, and 875 ms against 24 ms with a cold one.

Two indexes with the same two columns in a different order. For the same query, one reads 1,885 entries and the other 27,243. I measured why on a SQLite table of one million rows, and found that the leftmost-column rule has an exception called skip-scan.

I inserted one million rows into SQLite with 0, 1, 3 and 6 indexes. The table alone took 0.8 s and six indexes took 8.5 s, but the biggest cost was not the number of indexes: one index on a random column made SQLite pass 4 GB to write() for a 106 MB file.

SQLite says SCAN or SEARCH before it runs a query. I measured what the two words mean on one million rows: 16,469 pages against 7, and about 34 ms against 6 microseconds when the file is cached. With a cold cache the gap is much smaller.

I added one index to a SQLite table of one million users and read the file page by page. The index is a second sorted copy of the emails, each one with the rowid of its row, and when I broke one entry the database returned a row that does not exist.