Authors - Pranali Kosamkar, Gautam Sharma, Lakshya Upadhyaya, Bhavya Shah, Samarth Patel Abstract - In this paper, we will focus on optimization of MYSQL queries. Different factors are taken into account when calculating the cost of a query, such as the number of disk accesses, CPU processing time, and the number of rows scanned. Considering all this, we can create a price estimate, price analysis of all the different ways to write questions, metrics, constraint integrity, JOINS, nested queries, and other queries that come up when writing a lot of code from questions. Interfacing with the data logic and cost calculations is done in Python and is used with libraries like Matplotlib to find a more efficient comparison query writing strategy. MySQL's EXPLAIN keyword extracts execution details like the number of rows scanned and usage metrics and combines them with Python's timing module for execution time. These factors lead to the final price estimate and are plotted using graphs to help to draw conclusions. We also developed a MySQL user-defined function called calc_query_cost, which includes a formula that estimates query cost based on the number of queries, the number of rows scanned, the processing time, and usage metrics. Experimental results demonstrate that indexed queries were found to reduce the average query execution time by 70%-90%, with non-indexed queries taking up to 5x longer in certain scenarios also JOIN operations proved to be 2-3x more efficient than nested queries in terms of execution cost, especially for larger datasets. Optimizing query-writing strategies resulted in an execution time improvement of up to 60% for complex queries.
