keeferMar 2
BohwaZ

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'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&name=ext/expert

Command Line Shell For SQLite

Mar 1 at 10:29amWeb
Show threadBohwaZMar 1
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!
Show threadAliCat Tomskit’s Rawrrr Side 🐾Mar 2
@bohwaz this takes me back 20 years to using EXPLAIN SELECT in MySQL and the like 😺 (although having to then determine and add the indexes based on its output myself)
Show threadTim Ward ⭐🇪🇺🔶 #FBPEMar 1
@bohwaz Plenty have an "explain" feature which gives you the same information albeit less directly.
Show threadBohwaZMar 1
@TimWardCam
Nope explain doesn't tell you which index you should create. It just tells you what the query planner does.
Show threadTim Ward ⭐🇪🇺🔶 #FBPEMar 1
@bohwaz It's often enough blindingly obvious what you should do about it - I did say "less directly".
Show threadTheConstructor (he/him)Mar 2
@bohwaz @TimWardCam in SQL Server query plan gives you the CREATE INDEX SQL, if it thinks it's badly needed
Show threadLars Marowsky-Brée 😷Mar 1
@bohwaz Oh, I didn't know that. I did know about postgres's EXPLAIN, but that's somewhat less helpful.
Show threadDelfiMar 1
@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
Show threadBohwaZMar 1
@delsehi
Interesting. I never used SQL server though. But I don't think this exists in MySQL, postgre or Oracle.
Show threadCraig FrancisMar 1

@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

MySQL :: MySQL 9.6 Reference Manual :: 7.1.8 Server System Variables

Show threadBohwaZMar 1
@craigfrancis
Yup but it will not tell you which indexes should be created, you'll have to think about the query and try things yourself. Fine for a simple query, but when it's a 100 lines long... Slightly harder :)
Show threadCraig FrancisMar 1
@bohwaz true, and I think SQLite has some really cool features like that… personally I’m trying get MySQL and MariaDB to implement a very simple `WHERE id IN (?)` with a single placeholder for multiple values, as I still keep seeing people `implode(‘, ‘, $ids)` directly into the SQL string for some of that SQL Injection fun :-)
Show threadBohwaZMar 1
@craigfrancis
I'm personally using a custom ?? placeholder for this case. And I use implode, but before the call I'm quoting each value :)
Show threadCraig FrancisMar 1
@bohwaz yep, that’s probably fine, but I’m not worried about you, I’m worried about the vast majority of developers, and those who aren’t exactly writing the code themselves :-)
Show threadMatt FranzMar 2
@bohwaz now I'm going to have to check ClickHouse and DuckDb. 🤔
Show threadBarnibuMar 2
@bohwaz SQL Server has it too: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions
Show threadBohwaZMar 2
@barnibu
Interesting. Then I can correct my statement: the only open source database to have this :)
Show threadgrawityMar 5
@barnibu @bohwaz I remember seeing this style of diagram in StackOverflow's "public data" website (where you can do direct SQL queries), was wondering how it was generated...
Show threadBarnibuMar 5
@grawity @bohwaz yep, they're using SQL Server too and that graphic is rendered from an xml file that SQL Server returns if prompted via `set statistics xml on;`
Show threadmatthewcroughanMar 2
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.
Show threadPeter SanchezMar 2
@smlavine @bohwaz Woah thanks for the pro-tip. I had no idea about this feature.
Show threadMax CahillMar 5
@bohwaz thank you for tipping me off to this fantastic feature 👌 another reason to love sqlite
Show threadc-x-b  Mar 5
@bohwaz CockroachDB also does this for EXPLAIN
Show threadBohwaZMar 6
@cxberger Interesting, thanks.
Show threadclaudexMar 6
@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/
Show threadDaniel GibsonMar 6

@bohwaz
> No AI

yeah, that's why it's called `.expert` and not `.dumbfuck`