Citation:

Sarvesh Kumar Gupta, 2025. "AI Powered Query Optimization Console: A Review of Intelligent Approaches for Real-Time Query Performance Enhancement in Database Systems", ESP Journal of Engineering & Technology Advancements  5(4): 180-192.

Abstract:

Database systems are heavily used at the core of modern data-driven applications to efficiently process and manage large volumes of data. As the complexity of data and variability of workloads grow, conventional query optimization methods are frequently challenged to retain peak performance. In this regard, most existing solutions are conventional optimizers that form static cost models based on predefined rules (hints), which cannot well adapt to dynamic workloads and analytical queries. In response to these challenges, there has been a growing interest in incorporating artificial intelligence and machine learning techniques into the traditional database optimization frameworks.This review explores the unique idea of an AI Powered query optimization console, a system developed for database administrators and developers to understand query performance and recommends optimization workloads instantly. It describes the architecture, theoretical model and operational mechanisms of these systems, parts such as query monitoring engines, plan execution analyzer; Machine learning optimization module; Intelligent advice module for recommendation queries. Our experiments show that in comparison to the traditional optimization, AI-Aided Optimization can bring down the query execution time, increase throughput and improve resource utilization.The review also discusses some recent advancements in machine learning–based optimization of queries, most notably with reinforcement learning methods for optimizing join order and learned cost models for predicting the performance of query executions. In spite of these developments, challenges, including model interpretability and system integration as well as scalability issues that arise due to the unavailability of adequate training data, still remain. Tackling these obstacles is vital for the progression of powerful and trustworthy AI-based database management solutions.

References:

[1] Chaudhuri, S. (1998). An overview of query optimization in relational systems. Proceedings of the ACM SIGMOD International Conference on Management of Data, 34–43. https://doi.org/10.1145/276304.276314

[2] Garcia-Molina, H., Ullman, J. D., & Widom, J. (2009). Database systems: The complete book (2nd ed.). Pearson.

[3] Marcus, R., & Papaemmanouil, O. (2019). Deep reinforcement learning for join order enumeration. Proceedings of the ACM SIGMOD International Conference on Management of Data, 3–18. https://doi.org/10.1145/3299869.3300087

[4] Li, G., Zhou, X., & Li, J. (2021). Learning-based query optimization: A survey. IEEE Transactions on Knowledge and Data Engineering, 33(6), 2347–2365. https://doi.org/10.1109/TKDE.2019.2962004

[5] Van Aken, D., Pavlo, A., Gordon, G. J., & Zhang, B. (2017). Automatic database management system tuning through large-scale machine learning. Proceedings of the 2017 ACM International Conference on Management of Data (SIGMOD), 1009–1024. https://doi.org/10.1145/3035918.3064029

[6] Marcus, R., Negi, P., Mao, H., Tatbul, N., Alizadeh, M., Kraska, T., & Papaemmanouil, O. (2018). Neo: A learned query optimizer. Proceedings of the VLDB Endowment, 12(11), 1705–1718. https://doi.org/10.14778/3342263.3342644

[7] Pavlo, A., Angulo, G., Arulraj, J., Lin, H., Lin, J., Ma, L., & Stonebraker, M. (2019). Self-driving database management systems. Proceedings of the Conference on Innovative Data Systems Research (CIDR), 1–13.

[8] Marcus, R., & Papaemmanouil, O. (2020). Bao: Learning to steer query optimizers. Proceedings of the ACM SIGMOD International Conference on Management of Data, 1547–1561. https://doi.org/10.1145/3318464.3389720

[9] Kraska, T., Beutel, A., Chi, E., Dean, J., & Polyzotis, N. (2018). The case for learned index structures. Proceedings of the ACM SIGMOD International Conference on Management of Data, 489–504. https://doi.org/10.1145/3183713.3196909

[10] Zhang, J., Wang, Y., & Li, H. (2021). QueryBot 5000: Automatic database management system tuning using deep learning. IEEE Transactions on Knowledge and Data Engineering, 33(12), 3721–3733.

[11] Hellerstein, J. M., Stonebraker, M., & Hamilton, J. (2007). Architecture of a database system. Foundations and Trends in Databases, 1(2), 141–259. https://doi.org/10.1561/1900000002

[12] Ortiz, J., Balazinska, M., Gehrke, J., & Keerthi, S. (2023). Towards autonomous query optimization using machine learning. The VLDB Journal, 32(2), 295–312. https://doi.org/10.1007/s00778-022-00736-8

[13] Pavlo, A., Angulo, G., Arulraj, J., Lin, H., Lin, J., Ma, L., & Stonebraker, M. (2017). Self-driving database management systems. Conference on Innovative Data Systems Research (CIDR), 1–13.

[14] Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.Database System Concepts (7th ed.). McGraw-Hill.

[15] Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System Concepts (7th ed.). McGraw-Hill.

[16] Bruno, N., & Chaudhuri, S. (2007). Automatic physical database tuning: A relaxation-based approach. Proceedings of the ACM SIGMOD International Conference on Management of Data, 227–238. https://doi.org/10.1145/1247480.1247509

[17] Transaction Processing Performance Council. (2019). TPC benchmark H (decision support) standard specification. Transaction Processing Performance Council.

[18] Kraska, T., Beutel, A., Chi, E., Dean, J., & Polyzotis, N. (2018). The case for learned database systems. Proceedings of the ACM SIGMOD International Conference on Management of Data, 489–504.

[19] Stonebraker, M., & Çetintemel, U. (2005). “One size fits all”: An idea whose time has come and gone. Proceedings of the IEEE International Conference on Data Engineering, 2–11. https://doi.org/10.1109/ICDE.2005.1

[20] Chandola, V., Banerjee, A., & Kumar, V. (2009). Anomaly detection: A survey. ACM Computing Surveys, 41(3), 1–58. https://doi.org/10.1145/1541880.1541882

Keywords:

AI Powered Query Optimization, Database Performance Tuning, Machine Learning in Database Systems, Intelligent Query Advisor, Database Management Systems, Query Execution Optimization, Autonomous Databases, Performance Monitoring.