Advancements in SQL query optimization: a review of join order and index selection.
Uloženo v:
| Název: | Advancements in SQL query optimization: a review of join order and index selection. |
|---|---|
| Autoři: | Hettiarachchi, Nisuli, Yapa, Prasan |
| Informace o vydavateli: | Institute of Electrical and Electronics Engineers (IEEE) |
| Rok vydání: | 2025 |
| Sbírka: | OpenAIR@RGU (Robert Gordon University, Aberdeen) |
| Témata: | Multi-agent systems, SQL query optimization, Index selection, Join order optimization, Hybrid optimization techniques |
| Popis: | 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. |
| Druh dokumentu: | text |
| Jazyk: | English |
| Relation: | https://rgu-repository.worktribe.com/output/2981985 |
| DOI: | 10.1109/MLISE66443.2025.11100192 |
| Dostupnost: | https://doi.org/10.1109/MLISE66443.2025.11100192 https://rgu-repository.worktribe.com/file/2981985/1/HETTIARACHCHI%202025%20Advancements%20in%20SQL%20query%20%28AAM%29 https://rgu-repository.worktribe.com/output/2981985 |
| Rights: | openAccess ; https://creativecommons.org/licenses/by/4.0/ |
| Přístupové číslo: | edsbas.69BF6180 |
| Databáze: | BASE |
Nájsť tento článok vo Web of Science | Abstrakt: | 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. |
|---|---|
| DOI: | 10.1109/MLISE66443.2025.11100192 |