Improving MySQL query optimization with deep learning

For database systems, query optimization is a critical challenge with the ever-growing vast amounts of data. The efficiency of query optimization is crucial for the performance of database management systems, especially in large-scale applications where the complexity and volume of queries can significantly affect query execution time and resource utilization. Each database system has its own query plan structure and query optimization mechanism. Query optimizers of the database systems search for different query execution plans and try to estimate the optimal execution plan. These query optimizers use static and rule-based decision techniques that are very complex and difficult to manage and often struggle to handle the increasingly complex SQL queries used in modern applications. To address these challenges, this thesis proposes a framework for query optimization in MySQL databases using Tree Convolutional Neural Networks (TCNNs), a deep learning architecture used to leverage the hierarchical structure of query execution plans. For MySQL, database administrators can arrange optimizer behavior with the "optimizer-switch" system variable. The "optimizer-switch" variable's value is a set of flags or hints by another name. Each of these hints has the value of "on" or "off," which indicates this hint is enabled or disabled. The developed framework uses TCNNs to learn and arrange optimizer hints to improve query execution performance by predicting more efficient query execution plans. The model was evaluated against traditional optimization techniques. Experimental evaluation of the proposed approach demonstrates improvements in finding effective hints and decreasing query execution time.