Post #588286

2026-03-01 10:29 UTC

I am always amazed by the expert mode of the SQLite CLI. You type .expert Then you paste your SQL query. And #sqlite tells you which indexes you should create to speed up your query. No AI, no complex program to install. No expensive database architect to pay for. It&#39;s just clever programming. To my knowledge this is the only database in the world to have this feature. https://sqlite.org/cli.html#index_recommendations_sqlite_expert_ https://sqlite.org/src/dir?ci=trunk&amp;name=ext/expert

Replies (13)

@bohwaz@mamot.fr 2026-03-01 10:29

How it works is also clever: it analyses the where and order by clauses of the query, creates indexes based on that, retries the query and checks which indexes have been used. Simple but effective!

Open ##1768956
@TimWardCam@c.im 2026-03-01 19:45

@bohwaz Plenty have an "explain" feature which gives you the same information albeit less directly.

Open ##1768958
@larsmb@mastodon.online 2026-03-01 21:26

@bohwaz Oh, I didn't know that. I did know about postgres's EXPLAIN, but that's somewhat less helpful.

Open ##1768962
@delsehi@mastodon.nu 2026-03-01 21:34

@bohwaz I'm slightly confident that SQL Server has something like this too, but you get those recommendations by querying the proprietary version of INFORMATION_SCHEMA (or use the GUI in the Azure Portal). It's also based on how often some columns are used in queries, so it also uses some internal telemetry

Open ##1768963
@craigfrancis@mastodon.social 2026-03-01 22:58

@bohwaz tbh I’ve not used this, but MySQL does have the System Variable `log_queries_not_using_indexes`, which I assume would push you towards using an INDEX on all queries (but the docs suggest to me it’s more about queries that return all rows): https://dev.mysql.com/doc/refman/9.6/en/server-system-variables.html#sysvar_log_queries_not_using_indexes

Open ##1768965
@mdfranz@infosec.exchange 2026-03-02 00:26

@bohwaz now I'm going to have to check ClickHouse and DuckDb. 🤔

Open ##1768970
@barnibu@birdbutt.com 2026-03-02 08:38

@bohwaz SQL Server has it too: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions

Open ##1768971
@matthewcroughan@social.defenestrate.it 2026-03-02 08:43

Ah nice, a clever piece of code. A pattern that will be collected by the AI borg, transmogrified and assimilated into an AI codebase in the future.

Open ##1768975
@petersanchez@honk.petersanchez.com 2026-03-02 22:14

@smlavine @bohwaz Woah thanks for the pro-tip. I had no idea about this feature.

Open ##1768976
@maxc@merveilles.town 2026-03-05 08:25

@bohwaz thank you for tipping me off to this fantastic feature 👌 another reason to love sqlite

Open ##1768977
@cxberger@mastodon.boiler.social 2026-03-05 23:13

@bohwaz CockroachDB also does this for EXPLAIN

Open ##1768978
@claudex@pouet.chapril.org 2026-03-06 17:33

@bohwaz it's not included by default, but you can build something similar with postgres, but not integrated or as easy to use https://www.percona.com/blog/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/

Open ##1768980
@Doomed_Daniel@mastodon.gamedev.place 2026-03-06 17:42

@bohwaz > No AI yeah, that's why it's called `.expert` and not `.dumbfuck`

Open ##1768981