What? I wouldn’t want this. How would you evne automate it? Creating the right indexes is the same as creating the right tables and columns in your data model: It depends on the business purpose and usage of the data.

> Creating the right indexes is the same as creating the right tables and columns in your data model: It depends on the business purpose and usage of the data.

Right, so the way this works is that the database collects instrumentation data and, over time, automatically applies strategies (indexing improvements being one of them) to improve its performance.

https://arxiv.org/abs/2007.14244

As another user wrote, some db systems already can provide suggestions for missing indexes based on stats. But automatically creating them? Should all new indexes just add up (that would fill the db pretty fast) or replace prevoius ones (what about users depending on these indexes?)

Business usage is observed over time instead of known up front though. In a way this is sort of like the query planner. Couldn't your usage be observed and used to determine appropriate indexes?

i’d be more interested in an automatic index “suggester” based on observation and slow query analysis. there’s also the matter of new use cases where you’d absolutely want to be able to create them manually.

It has been a million years since I've used it, but I think MS SQL Server has this.

Here's some documentation I just found (at https://docs.microsoft.com/en-us/sql/relational-databases/pe...):

> The Missing Indexes report shows potentially missing indexes that the Query Optimizer identified during query compilation. However, these recommendations should not be taken at face value. Microsoft recommends that indexes with a score greater than 100,000 should be evaluated for creation, as those have the highest anticipated improvement for user queries.

> Tip

> Always evaluate if a new index suggestion is comparable to an existing index in the same table, where the same practical results can be achieved simply by changing an existing index instead of creating a new index. For example, given a new suggested index on columns C1, C2 and C3, first evaluate if there is an existing index over columns C1 and C2. If so, then it may be preferable to simply add column C3 to the existing index (preserving the order of pre-existing columns) to avoid creating a new index.

Absolutely. I'm increasingly in favor of generated code you check into source control.

Just spitballing, it sounds plausible to infer the correct index from the set of common queries.

Edit: Which I guess is basically what the sibling commenters said.

I could see it as taking a typical day's worth of transactions, creating new test db from this log, and then letting a tool try out different indexes based on some common heuristics and re-run the queries and benchmark. Because often it requires experimentation to get the right indexes.