BohwaZMar 1

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

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 threadBohwaZ
@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 :)
Mar 1 at 11:18pmWeb
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 :-)