SQLite Forum

ROWID primary key works slower than index
I can't answer all of of your questions, but in this case the blob size vs the covering index is important. In the main B tree each record has the rowid _and_ the blob for every value in the tree. With a 1K blob then you're only gonna get 3 records per 4k page (or use overflow pages) for the main storage.

Conversely the index on id _only_ stores id, it doesn't store the blob in the index. Since it's only storing one integer for each record then you're gonna store thousands per page.

Since your query doesn't use any of the other fields, it doesn't have to go to the main table to do the query, it can get everything it needs from the nice compact index, which in this specific case means it's fetching a thousand times fewer pages from the database to complete it. If your query involved any of the other fields in the table, then the extra index on ID is going to be slower and not of any use.