research-article

CoPhy: a scalable, portable, and interactive index advisor for large workloads

Editors: José Blakeley, Joseph M. Hellerstein, Nick Koudas, Wolfgang Lehner, Sunita Sarawagi, Uwe Röhm Authors:

Debabrata Dash,

Neoklis Polyzotis,

Anastasia AilamakiAuthors Info & Claims

Proceedings of the VLDB Endowment, Volume 4, Issue 6

Pages 362 - 372

https://doi.org/10.14778/1978665.1978668

Published: 01 March 2011 Publication History

Abstract

Index tuning, i.e., selecting the indexes appropriate for a workload, is a crucial problem in database system tuning. In this paper, we solve index tuning for large problem instances that are common in practice, e.g., thousands of queries in the workload, thousands of candidate indexes and several hard and soft constraints. Our work is the first to reveal that the index tuning problem has a well structured space of solutions, and this space can be explored efficiently with well known techniques from linear optimization. Experimental results demonstrate that our approach outperforms state-of-the-art commercial and research techniques by a significant margin (up to an order of magnitude).

References

[1]

S. Agrawal, S. Chaudhuri, L. Koll&#225;r, A. P. Marathe, V. R. Narasayya, and M. Syamala. Database tuning advisor for microsoft sql server 2005. In VLDB, pages 930--932, 2008.

Digital Library

[2]

S. Boyd and L. Vandenberghe. Convex Optimization. Cambridge University Press, March 2004.

Digital Library

[3]

N. Bruno and S. Chaudhuri. Automatic physical database tuning: a relaxation-based approach. In SIGMOD, pages 227--238, 2005.

Digital Library

[4]

N. Bruno and S. Chaudhuri. Constrained physical design tuning. PVLDB, 1(1):4--15, 2008.

Digital Library

[5]

N. Bruno and R. V. Nehme. Configuration-parametric query optimization for physical design tuning. In SIGMOD, pages 941--952, 2008.

Digital Library

[6]

A. Caprara and J. Salazar. A branch-and-cut algorithm for a generalization of the uncapacitated facility location problem. TOP, 1(4):135--163, 1996.

[7]

S. Chaudhuri, M. Datar, and V. Narasayya. Index selection for databases: A hardness study and a principled heuristic solution. IEEE TKDE, 16(11):1313--1323, 2004.

Digital Library

[8]

S. Chaudhuri and V. Narayasa. Program for tpc-d data generation with skew. ftp://ftp.research.microsoft.com/users/viveknar/tpcdskew.

[9]

C. Daskalakis, I. Diakonikolas, and M. Yannakakis. How good is the chord algorithm? In SODA, pages 978--991, 2010.

Digital Library

[10]

S. J. Finkelstein, M. Schkolnick, and P. Tiberio. Physical database design for relational databases. ACM TODS, 13(1):91--128, 1988.

Digital Library

[11]

M. L. Fisher. The lagrangian relaxation method for solving integer programming problems. Manage. Sci., pages 1861--1871, 2004.

Digital Library

[12]

V. Y. Lum and H. Ling. An optimization problem on the selection of secondary keys. In Proceedings of the 26th annual conference, pages 349--356, 1971.

Digital Library

[13]

S. Papadomanolakis. Large-scale data management for the sciences. PhD thesis, CMU, 2007.

[14]

S. Papadomanolakis and A. Ailamaki. An integer linear programming approach to automated database design. In SMDB, pages 442--449, 2007.

Digital Library

[15]

S. Papadomanolakis, D. Dash, and A. Ailamaki. Efficient use of the query optimizer for automated physical design. In VLDB, pages 1093--1104, 2007.

Digital Library

[16]

N. Reddy and J. R. Haritsa. Analyzing plan diagrams of database query optimizers. In VLDB, pages 1228--1240, 2007.

Digital Library

[17]

K. Schnaitter and N. Polyzotis. A Benchmark for Online Index Selection. In SMDB, pages 1701--1708, 2009.

Digital Library

[18]

P. Selinger, M. Astrahan, D. Chamberlin, R. Lorie, and T. Price. Access path selection in a relational database management system. In SIGMOD, pages 23--34, 1979.

Digital Library

[19]

Z. A. Talebi, R. Chirkova, Y. Fathi, and M. Stallmann. Exact and inexact methods for selecting views and indexes for olap performance improvement. In EDBT, pages 311--322, 2008.

Digital Library

[20]

D. C. Zilio, J. Rao, S. Lightstone, G. M. Lohman, A. Storm, C. Garcia-Arellano, and S. Fadden. Db2 design advisor: Integrated automatic physical database design. In VLDB, pages 1087--1097, 2004.

Digital Library

Cited By

Wu W(2025)Hybrid Cost Modeling for Reducing Query Performance Regression in Index TuningIEEE Transactions on Knowledge and Data Engineering10.1109/TKDE.2024.348495437:1(379-391)Online publication date: 1-Jan-2025
https://dl.acm.org/doi/10.1109/TKDE.2024.3484954
Lai SWu XPeng Z(2025)E2RLIXT: An end-to-end framework for robust index tuning based on reinforcement learningComputers and Electrical Engineering10.1016/j.compeleceng.2024.109958122(109958)Online publication date: Mar-2025
https://doi.org/10.1016/j.compeleceng.2024.109958
Gerasimov VDusheba V(2024)Analysis of Optimizing Database Performance MethodsÈlektronnoe modelirovanie10.15407/emodel.46.06.04346:6(43-54)Online publication date: 19-Dec-2024
https://doi.org/10.15407/emodel.46.06.043
Show More Cited By

Index Terms

CoPhy: a scalable, portable, and interactive index advisor for large workloads
1. Information systems
  1. Data management systems
    1. Database design and models
    2. Database management system engines
  2. Information retrieval
    1. Document representation
    2. Search engine architectures and scalability
      1. Search engine indexing

Recommendations

The hyperdyadic index and generalized indexing and query with PIQUE
SSDBM '15: Proceedings of the 27th International Conference on Scientific and Statistical Database Management

Many scientists rely on indexing and query to identify trends and anomalies within extreme-scale scientific data. Compressed bitmap indexing (e.g., FastBit) is the go-to indexing method for many scientific datasets and query workloads. Recently, the ...
Comparison between the zeroth-order Randić index and the sum-connectivity index

The zeroth-order Randić index and the sum-connectivity index are very popular topological indices in mathematical chemistry. These two indices are based on vertex degrees of graphs and attracted a lot of attention in recent years. Recently Li and Li (...
Transform-Space View: Performing Spatial Join in the Transform Space Using Original-Space Indexes

Spatial joins find all pairs of objects that satisfy a given spatial relationship. In spatial joins using indexes, original-space indexes such as the R-tree are widely used. An original-space index is the one that indexes objects as represented in the ...

Comments

Information & Contributors

Information

Published In

cover image Proceedings of the VLDB Endowment

Proceedings of the VLDB Endowment Volume 4, Issue 6

March 2011

71 pages

ISSN:2150-8097

Issue’s Table of Contents

Publisher

VLDB Endowment

Publication History

Published: 01 March 2011

Published in PVLDB Volume 4, Issue 6

Qualifiers

Research-article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

63
Total Citations
View Citations
226
Total Downloads

Downloads (Last 12 months)16
Downloads (Last 6 weeks)2

Reflects downloads up to 16 Jan 2025

Other Metrics

View Author Metrics

Citations

Cited By

Wu W(2025)Hybrid Cost Modeling for Reducing Query Performance Regression in Index TuningIEEE Transactions on Knowledge and Data Engineering10.1109/TKDE.2024.348495437:1(379-391)Online publication date: 1-Jan-2025
https://dl.acm.org/doi/10.1109/TKDE.2024.3484954
Lai SWu XPeng Z(2025)E2RLIXT: An end-to-end framework for robust index tuning based on reinforcement learningComputers and Electrical Engineering10.1016/j.compeleceng.2024.109958122(109958)Online publication date: Mar-2025
https://doi.org/10.1016/j.compeleceng.2024.109958
Gerasimov VDusheba V(2024)Analysis of Optimizing Database Performance MethodsÈlektronnoe modelirovanie10.15407/emodel.46.06.04346:6(43-54)Online publication date: 19-Dec-2024
https://doi.org/10.15407/emodel.46.06.043
Halfpap SKossmann JSchlosser RMarkl V(2024)Looking Deeply into the Magic Mirror: An Interactive Analysis of Database Index Selection ApproachesProceedings of the VLDB Endowment10.14778/3685800.368586017:12(4301-4304)Online publication date: 8-Nov-2024
https://dl.acm.org/doi/10.14778/3685800.3685860
Kesarwani MHaritsa J(2024)Index Advisors on Quantum PlatformsProceedings of the VLDB Endowment10.14778/3681954.368202517:11(3615-3628)Online publication date: 30-Aug-2024
https://dl.acm.org/doi/10.14778/3681954.3682025
Zhou WLin CZhou XLi G(2024)Breaking It Down: An In-Depth Study of Index AdvisorsProceedings of the VLDB Endowment10.14778/3675034.367503517:10(2405-2418)Online publication date: 6-Aug-2024
https://dl.acm.org/doi/10.14778/3675034.3675035
Yu TZou ZSun WYan Y(2024)Refactoring Index Tuning Process with Benefit EstimationProceedings of the VLDB Endowment10.14778/3654621.365462217:7(1528-1541)Online publication date: 30-May-2024
https://dl.acm.org/doi/10.14778/3654621.3654622
Zhu RWeng LWei WWu DPeng JWang YDing BLian DZheng BZhou J(2024)PilotScope: Steering Databases with Machine Learning DriversProceedings of the VLDB Endowment10.14778/3641204.364120917:5(980-993)Online publication date: 2-May-2024
https://dl.acm.org/doi/10.14778/3641204.3641209
Mozaffari MDignös AGamper JStörl U(2024)Self-tuning Database Systems: A Systematic Literature Review of Automatic Database Schema Design and TuningACM Computing Surveys10.1145/366532356:11(1-37)Online publication date: 29-Jun-2024
https://dl.acm.org/doi/10.1145/3665323
Wang XWu WWang CNarasayya VChaudhuri S(2024)Wii: Dynamic Budget Reallocation In Index TuningProceedings of the ACM on Management of Data10.1145/36549852:3(1-26)Online publication date: 30-May-2024
https://dl.acm.org/doi/10.1145/3654985
Show More Cited By

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Article

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Issue’s Table of Contents