Advancements in SQL Query Optimization: A Review of Join Order and Index Selection

Efficient SQL Query Optimization (QO) is a fundamental aspect of database management systems, aimed at enhancing query performance and reducing resource consumption typically involves selecting the most efficient execution plan for a given query, considering factors such as join order, access methods, and the use of indexes. This review paper focuses on two key agents in SQL QO: the Join Order Agent (JOA) and the Index Selection Agent (ISA). The JOA seeks to determine the optimal sequence for joining multiple tables, minimizing intermediate results and improving query execution time. The ISA identifies the most effective indexes to speed up data retrieval, considering various database schema configurations and query patterns. We review existing approaches for both agents, including heuristic-based methods, cost-based models, and machine learning (ML) techniques. The paper also highlights the challenges faced in these areas, such as the scalability of existing methods, the need for dynamic adaptation to changing workloads, and the integration of multiple optimization strategies. Finally, the paper discusses how multi-agent systems (MAS), and hybrid optimization techniques can address these challenges, offering significant improvements in QO for modern relational DBMS.