Enhancing Database Performance through a Comparison of Traditional and AI-Driven Query Optimization Techniques

Query optimization is a fundamental aspect of database management systems (DBMS), crucial for enhancing query performance and resource utilization. Traditional methods for optimising queries can handle most cases, but they are inflexible in dealing with difficult or highly dynamic query workloads. New approaches in AI and ML are now available to help overcome these challenges, supporting more flexible optimization methods. This paper presents a comparative study of traditional and AI-driven query optimization approaches. Using synthetic data, we evaluate multiple machine learning models, including CatBoost, LightGBM, and ExtraTrees, against a traditional rule-based baseline that relies on indexing heuristics. The results show that AI-driven models, particularly CatBoost, achieved an accuracy of 59%, outperformed traditional methods (48%) in terms of accuracy, precision, recall, and F1-score, highlighting their ability to capture complex feature interactions and improve query execution efficiency. Despite the promising performance of AI models, the paper also discusses the trade-offs involved, including the interpretability challenges and computational overhead associated with AI-based approaches. This study demonstrates the potential of AI-driven optimizers as a valuable tool for modern DBMSs, particularly in dynamic and high-complexity environments.