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