I am always amazed by the expert mode of the SQLite CLI.
Uncategorized
21
Posts
12
Posters
0
Views
-
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 -
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/expertHow 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!
-
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@bohwaz Plenty have an "explain" feature which gives you the same information albeit less directly.
-
@bohwaz Plenty have an "explain" feature which gives you the same information albeit less directly.
@TimWardCam
Nope explain doesn't tell you which index you should create. It just tells you what the query planner does. -
@TimWardCam
Nope explain doesn't tell you which index you should create. It just tells you what the query planner does.@bohwaz It's often enough blindingly obvious what you should do about it - I did say "less directly".
-
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@bohwaz Oh, I didn't know that. I did know about postgres's EXPLAIN, but that's somewhat less helpful.
-
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@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
-
@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
@delsehi
Interesting. I never used SQL server though. But I don't think this exists in MySQL, postgre or Oracle. -
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@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):
-
@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):
@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 :) -
@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 :)@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 :-)
-
@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 :-)
@craigfrancis
I'm personally using a custom ?? placeholder for this case. And I use implode, but before the call I'm quoting each value :) -
@craigfrancis
I'm personally using a custom ?? placeholder for this case. And I use implode, but before the call I'm quoting each value :)@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 :-)
-
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@bohwaz now I'm going to have to check ClickHouse and DuckDb. 🤔
-
@TimWardCam
Nope explain doesn't tell you which index you should create. It just tells you what the query planner does.@bohwaz @TimWardCam in SQL Server query plan gives you the CREATE INDEX SQL, if it thinks it's badly needed
-
undefined oblomov@sociale.network shared this topic on
-
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!
@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)
-
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 -
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/expertAh 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. -
@barnibu
Interesting. Then I can correct my statement: the only open source database to have this :) -
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
Gli ultimi otto messaggi ricevuti dalla Federazione
-
Trump wants regime change in Iran. History suggests that could lead to a long, complicated struggle
-
@stefanocovino ha già detto e scritto tutto chi un avolta usava la testa: oggi è l'epoca del bla bla bla.
-
This post did not contain any content.
-
@carson great, thank you!!!
-
La mamma è sempre la mamma...
Zucchero ovunque, e non parlo del cantante. Oggi devo riparare il telefono al mio vicino sennò andavo ad aspirarla
-
@andre123 si si ma con debian ci va un po
-
Sostenibilità e sovranità digitale nella scuola del 21° secolo. Convegno in occasione dei 20 anni del Progetto FUSS
Il Progetto #FUSS (Free Upgrade for a Digitally Sustainable School) celebra quest’anno il suo ventesimo anniversario (2005-2025) e per l'occasione organizzerà un convegno lunedì 20 aprile 2026 dalle 15:00 alle 18:00 presso l’Aula Magna della Facoltà di Ingegneria della Libera Università di #Bolzano in via Bruno Buozzi 1 a Bolzano.
-
@rl_dane ah ok, thanks. I was looking at apps but everything in the AppStore looks like slop. On desktop I like Cloudflare Speed Test, but the amount of stuff displayed is too much for mobile :P I'll bookmark fast.com, thanks!
