2 ms·

> It's worth being careful to only add indexes that will be used by real queries. This reminds me of a technique used by Google App Engine SDK a long time ago

by kccqzy 1y ago

> It's worth being careful to only add indexes that will be used by real queries. This reminds me of a technique used by Google App Engine SDK a long time ago before it was called cloud. Basically in development mode, the SDK captures the kind of queries you make, and then automatically add any index that would speed up this query into a configuration file. You then later deploy with this configuration file, which tells the production data store to create such indexes. I thought this was a genius idea. Not sure if something similar exists for SQLite.

deleted 1y ago
[deleted]
Waterluvian 1y ago
This seems like the kind of tool that would be useful for all indexable databases. I imagine you can’t definitively know and therefore just make this automagical. But I bet the result is a pretty solid shortlist for consideration.
emschwartz 1y ago
The SQLite CLI has a `.expert` command that will give index recommendations when you run queries: https://sqlite.org/cli.html#index_recommendations_sqlite_expert_ https://sqlite.org/cli.html#index_recommendations_sqlite_exp... It's not quite the same as capturing all of the queries used in development (or production), but it seems somewhat useful. I'll also note that I had an LLM generate quite a useful script to identify unused indexes (it scanned the code base for SQL queries, ran `EXPLAIN QUERY PLAN` on each one to identify which indexes were being used, and cross-referenced that against the indexes in the database to find unused ones). It would probably be possible to do something similar (but definitely imperfect) where you find all of the queries, get the query plans, and use an LLM to make suggestions about what indexes would speed up those queries.
- zeroq 1y ago
  wow! I used to bruteforce a bunch of indexes until EXPLAIN on queries gave satisfactory results! I actually looked for a tool where I could provide a schema and all queries to get optimal indexes but never found one that actually worked.
crazygringo 1y ago
Every index you add uses a whole lot more disk space (a big deal) and slows down writes (usually not a big deal). The idea of a tool automatically adding indexes scares me a little. Sometimes you specifically want a query to take 0.003s instead of 0.001 because you don't want to use another 10GB of disk space.

by kccqzy 1y ago

deleted 1y ago

[deleted]

Waterluvian 1y ago

This seems like the kind of tool that would be useful for all indexable databases. I imagine you can’t definitively know and therefore just make this automagical. But I bet the result is a pretty solid shortlist for consideration.

emschwartz 1y ago

The SQLite CLI has a `.expert` command that will give index recommendations when you run queries: https://sqlite.org/cli.html#index_recommendations_sqlite_expert_ https://sqlite.org/cli.html#index_recommendations_sqlite_exp... It's not quite the same as capturing all of the queries used in development (or production), but it seems somewhat useful. I'll also note that I had an LLM generate quite a useful script to identify unused indexes (it scanned the code base for SQL queries, ran `EXPLAIN QUERY PLAN` on each one to identify which indexes were being used, and cross-referenced that against the indexes in the database to find unused ones). It would probably be possible to do something similar (but definitely imperfect) where you find all of the queries, get the query plans, and use an LLM to make suggestions about what indexes would speed up those queries.

zeroq 1y ago
wow! I used to bruteforce a bunch of indexes until EXPLAIN on queries gave satisfactory results! I actually looked for a tool where I could provide a schema and all queries to get optimal indexes but never found one that actually worked.

crazygringo 1y ago

Every index you add uses a whole lot more disk space (a big deal) and slows down writes (usually not a big deal). The idea of a tool automatically adding indexes scares me a little. Sometimes you specifically want a query to take 0.003s instead of 0.001 because you don't want to use another 10GB of disk space.