Optimizing the Execution Time of JOIN Queries and Subqueries Using MySQL

Abstract

Relational database systems form the backbone of modern information management. However, the escalating volumes of data and increasing complexity of queries present substantial performance challenges in data retrieval operations. This study investigates the execution time differences between Subqueries and five join methods: Inner Join, Left Join, Right Join, AsOf Join and Lateral Join, in MySQL environments. An experimental methodology was employed, utilising two simulated relational tables containing 100, 1,000, and 10,000 rows of data. Each query method was executed three times under identical system conditions to establish reliable average execution times. The findings demonstrate that join operations substantially outperform subqueries across all tested datasets. Inner Join, Left Join and Right Join maintained execution times below 0.04 seconds, even with the most extensive dataset. Conversely, subqueries exhibited significant performance degradation, with execution times increasing to tens of seconds as the data volume increased. This performance disparity stems from the iterative processing inherent to subqueries, which intensifies proportionally with dataset scale, whereas join operations leverage more efficient simultaneous data processing and merging algorithms. The research concludes that join methods constitute the more appropriate choice for medium to large-scale data scenarios, offering practical optimisation guidance for database developers and administrators implementing MySQL-based systems.