OptimAIzerSQL: Optimizing SQL Queries with Heuristic and ML-Based Multi-Agent Systems

SQL query optimization (QO) is a critical component of database management systems, essential for improving query execution times, enhancing resource utilization, and ensuring scalability in the face of growing data volumes. This study presents OptimAIzerSQL, an innovative multi-agent system that leverages both heuristic-based and machine learning (ML) approaches to address the limitations of traditional optimizers. The system integrates three specialized agents: a heuristic-based Join Order Agent (JOA) to optimize table join sequences and a ML-driven Index Selection Agent (ISA) utilizing reinforcement learning (RL) for dynamic index recommendations. The effectiveness of OptimAIzerSQL is demonstrated using the IMDb dataset, a well-structured benchmark, showcasing significant improvements in execution time, cost efficiency, and query performance. Key evaluation metrics such as reward-based execution time reduction, prediction accuracy, and cost savings evident the system's capabilities. By combining heuristic efficiency with the adaptability of ML, this research bridges gaps in traditional SQL optimization techniques, offering a scalable and robust solution for modern database workloads. The proposed framework provides valuable insights into how multi-agent systems can revolutionize database optimization by delivering tailored, high-performance query plans for complex real-world scenarios.