On a self-tuning index recommendation approach for databases
Parinaz Ameri · 2016
Due to wide usage of databases and ever increasing size of their datasets and workloads, tuning the physical database design to improve performance with regard to specific workloads is crucial. An important aspect of physical database design is selecting fitting indexes to improve run-time of a workload. The proper set of indexes should be chosen by considering ratio of read to write of workload operations, selectivity of attributes and resource limitations. Since databases have their own cost-functions implemented in their query optimizer, a reasonable way of choosing indexes is to present a limited number of candidate indexes to the optimizer and recommend its chosen ones. However, this selection process should not put too much load on the in-production system. Therefore, the focus of this thesis is on utilizing scalable algorithms to propose minimum and yet required number of the candidate indexes for each workload to the query optimizer. We recommend indexes only for longest and most frequent queries in the workload. The indexes are prioritized by their coverage for more queries, existence in the system and selectivity of their attributes in the dataset. We also propose a method to avoid interference of the index recommendation process with other applications.