SQLite's Automatic Indexes

Related: <a href="https:&#x2F;&#x2F;sqlite.org&#x2F;cli.html#index_recommendations_sqlite_expert_" rel="nofollow">https:&#x2F;&#x2F;sqlite.org&#x2F;cli.html#index_recommendations_sqlite_exp...</a><p>There&#x27;s an experimental module and CLI command in the normal shell to use this logic to dump out a suggested list of indexes to create for a given SQL command to speed it up.

I was not aware that SQLite creates temporary indexes in places where other databases would perform merge joins.<p><a href="https:&#x2F;&#x2F;sqlite.org&#x2F;tempfiles.html#transient_indices" rel="nofollow">https:&#x2F;&#x2F;sqlite.org&#x2F;tempfiles.html#transient_indices</a><p>SQLite also creates background indexes for ROWID, of which I was aware.<p><a href="https:&#x2F;&#x2F;sqlite.org&#x2F;rowidtable.html" rel="nofollow">https:&#x2F;&#x2F;sqlite.org&#x2F;rowidtable.html</a>

I think EXPLAIN is a little known feature that gives a lot of insight about database internals, especially in Sqlite.<p>Funny you posted this now, I just recently researched Sqlite internals for my own project. Maybe you will find it interesting: <a href="https:&#x2F;&#x2F;twitter.com&#x2F;high_byte&#x2F;status&#x2F;1607853384123703296" rel="nofollow">https:&#x2F;&#x2F;twitter.com&#x2F;high_byte&#x2F;status&#x2F;1607853384123703296</a><p>Interesting blog, I love that you keep consistency writing for so long.

Cool details. Makes sense, since a database has to have index creation anyway, and if you&#x27;re avoiding merge joins for code complexity reasons, why not use it to optimize.<p>An interesting project to manage indexes on postgres that seems tangentially related:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;ankane&#x2F;dexter">https:&#x2F;&#x2F;github.com&#x2F;ankane&#x2F;dexter</a>

As others already noted, this is equivalent to asymmetric hash join, except less efficient. Constructing a hash table on the fly instead of a B-tree would almost certainly be faster to both build and query. You don’t need an ordered lookup structure for join indexes (unless it’s a merge join, which SQLite doesn’t do anyway).

That was interesting to read. RavenDB does something similar, if there isn&#x27;t an index for the query, it will create one for you. The difference is that this is a persistent structure, which is reused across multiple queries and invocations.

It might make sense to flag in the metadata that an index might be needed; then again someone using SQLite will probably know very well which queries are being executed.<p>Also, most of the time joins are made on foreign keys and these should always be indexed. So I guess this should be a rare occurrence if the database is properly thought-out.

I keep seeing posts like these, and I&#x27;m wondering, whether &quot;lite&quot; still applies. It feels like all it is missing is the network layer and it would have more features than original MySQL.

EDIT: I misread. Nevermind!