SQLite Forum

Automatic indexing (idle question)
Nice Idea. Full-self-driving for SQL Engines.
I suspect however it will be much more delayed than the Tesla version.

The problem I think is that the Query planner and expert mode suggestions require a posed query to assess.

The only realistic way a DB can be full-self-driving is if every column in every table was also an index, and, every combination of columns (all permutations) up to and including the full set of covering indexes.

Obviously this would grow DB data exponentially, but perhaps if we could prune down the needed indexes....

To prune that list you have to consider removing (or not-adding) every index. To remove any index IA on column A (or any Index IABC on columns A, B and C), you have to answer the question:   
"Can I guarantee that there will never be a circumstance where querying by this specific column (or combination of columns) will be useful/appropriate?"

Of course that is not an answer that can be known algorithmically at design-time. Perhaps a sort-of learning algorithm can observe the DB in-use for some time and prune the never-used indexes. But for this to be acceptable you have to ensure that any query that will ever be needed of the DB is run within that learning time-frame, and as Gunter already pointed out with his World-ending analogy, that is not possible or even feasible.

Perhaps then a kind of mode where the requirement states: "Run the learning algorithm while using the DB for every possible query that may be needed of it" to produce a robust set of indexes that is guaranteed to service at least those queries. I feel like this can already be achieved by a script taking as input a DB, a set of Queries, and outputs the distinct list of indexes obtained from running said queries using the CLI .expert mode.

Actually, that may be a worthwhile script to design. Perhaps with the added function option of simply adding those indexes. Not sure I would blindly trust it myself, but may be a good start.