A Survey on Query Performance Optimization by Index Recommendation

Abstract

Query language access data from databases. With exponential growth of data, optimization techniques need to be adopt for better results. Query performance tuning and optimization can be achieved by query reformation and index selection. Searching tuples from millions of results is overhead and it degrades overall system performance. To reduce searching time is goal of index recommendation. Index Selection Problem (ISP) is optimization problem. This is NPH problem and it can be solve by different approaches like greedy approach, dynamic programming, linear programming, branch and bound, genetic algorithm, etc. In general, indexing is done on candidate keys but it will not give assurance of optimal solution. Researchers tried to resemble ISP with knapsack problem and variation of it. Different data structure are used for indexing like tree, hash, bitmap, etc. In composite column indexes, order of columns affects overall performance In-memory databases are fast databases and new data structures to be suggest for indexing. Usually indexing is done on only columns which will yield profit in query execution. Join operations executions are discussed briefly. General Terms Database performance optimization and tuning