Hacker News Reader

Comment by kccqzy | Hacker News Reader

> 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.

View on HN

The SQLite CLI has a `.expert` command that will give index recommendations when you run queries: 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.

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.

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.

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.

Reply on news.ycombinator.com