The week's pick
Reseach Article
A Survey on Query Performance Optimization by Index Recommendation
| International Journal of Computer Applications |
| Foundation of Computer Science (FCS), NY, USA |
| Volume 113 - Number 19 |
| Year of Publication: 2015 |
| Authors: Pratham L. Bajaj |
10.5120/19937-2091
|
Pratham L. Bajaj . A Survey on Query Performance Optimization by Index Recommendation. International Journal of Computer Applications. 113, 19 ( March 2015), 36-40. DOI=10.5120/19937-2091
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.
References
- J. Calle, Y. Sáez and D. Cuadra,"An Evolutionary Approach to the Index Selection Problem," Nature and Biologically Inspired Computing (NaBIC) IEEE 2011, pp. 485 - 490.
- Y. E. Ioannidis," Randomized Algorithms for optimizing large Join Queries," in SIGMOD '90 Proceedings of the 1990 ACM SIGMOD international conference on Management of data, pp 312-321.
- A. Hameurlain and F. Morvan, "Evolution of Query Optimization Methods", Springer Trans. on Large-Scale Data & Knowledge, pp. 211–242, 2009.
- H. Molina,"Main Memory Database Systems: An Overview," IEEE Transaction on Knowledge and data engineering, vol 4 No. 6, December 1992.
- Oracle "Performance Tuning Guide 11g Release 2".
- S. Chaudhuri and V. Narasayya, "An Efficient, Cost-Driven Index Selection Tool for Microsoft SQL Server", Proceeding of the 23rd VLDB Conference, 1997, pp 146-155.
- S. Papadomanolakis and A, Ailamaki in "An Integer Linear Programming Approach to Database Design" in International Conference on Data Engineering Workshop, 2007, pp 442-449.
- H. Gupta, V. Harinarayan and A. Rajaraman, "Index Selection for OLAP", in International Conference on Data Engineering, 1997, pp 208-219.
- S. Chaudhuri, V. Narasayya, "Self-Tuning Database Systems: A Decade of Progress" in VLDB Endowment, ACM 978-1-59593-649 2009.
- P. Kolaczkowski, and H. Rybinski, "Automatic Index Selection in RDBMS by Exploring Query Execution Plan Space," Springer SCI 223, 2009, pp. 3-24.
- S. Chaudhuri, "Index Selection for Databases: A Hardness Study and a Principled Heuristic Solution" in IEEE transactions on knowledge and data engineering, vol. 16, no. 11, 2004.
- G. Valentin, M. Zuliani, D. C. Zilio, A. Skelley and G. Lohman, "DB2 Advisor: An Optimizer Smart Enough to Recommend Its Own Indexes".
- S. Vellev "Review of Algorithms for the Join Ordering Problem in Databases Query Optimization".
- S. Chaudhuri "An Overview of Query Optimization in Relational Systems", in ACM SIGACT-SIGMOD- SIGART symposium on Principles of database systems, 1998, pp 34-43.
Index Terms
Keywords