Comparative Study of Join Algorithms in MySQL: Cross, Inner, Outer, and Self Joins
DOI:
https://doi.org/10.71426/jcdt.v1.i1.pp1-9Keywords:
Structured Query Language (SQL), SQL joins, MySQL Performance, Query optimization, DatabaseAbstract
A Structured Query Language (SQL) joins are fundamental to relational database operations, merging data from multiple tables into coherent result sets. This comparative study examines the performance characteristics of cross joins, inner joins, left/right/full outer joins, and self-joins in MySQL under large-scale workloads. Experiments were conducted on datasets ranging from 10,000 to 50 million rows using execution time, CPU utilization, memory consumption, and scalability as metrics. Results demonstrate inner joins achieve superior performance when properly indexed; outer joins incur moderate overhead for handling unmatched rows; self-joins perform comparably to inner joins with effective indexing but degrade under multi-level recursion; cross joins exhibit exponential growth and should be used sparingly. Recommendations for index strategies and query design are provided.
References
[1] Kepner J, Gadepally V, Hutchison D, Jananthan H, Mattson T, Samsi S, Reuther A. Associative array model of SQL, NoSQL, and NewSQL Databases. In2016 IEEE High Performance Extreme Computing Conference (HPEC) 2016 Sep 13 (pp. 1-9). IEEE. 10.1109/HPEC.2016.7761647
[2] Hannula M, Zhang Z, Song BK, Link S. Discovery of cross joins. IEEE Transactions on Knowledge and Data Engineering. 2022 Jul 21;35(7):6839-51. DOI: 10.1109/TKDE.2022.3192842
[3] MySQL AB. MySQL Reference Manual 8.0. Oracle, 2023.
[4] Krogh JW. MySQL 8 query performance tuning: a systematic method for improving execution speeds. Apress; 2020 Mar 16. DOI: https://doi.org/10.1007/978-1-4842-5584-1
[5] M. Brown, MySQL 8.0 Performance Tuning, 2nd ed., O’Reilly Media, 2023.
[6] Al Saedi AK, Deris MB. An efficient multi join query optimization for DBMS using swarm intelligent approach. In2014 4th World Congress on Information and Communication Technologies (WICT 2014) 2014 Dec 8 (pp. 113-117). IEEE. DOI: 10.1109/WICT.2014.7077312
[7] Krogh JW. The Query Optimizer. InMySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds 2020 Mar 17 (pp. 417-485). Berkeley, CA: Apress. DOI: https://doi.org/10.1007/978-1-4842-5584-1_17
[8] Hannula M, Zhang Z, Song BK, Link S. Discovery of cross joins. IEEE Transactions on Knowledge and Data Engineering. 2022 Jul 21;35(7):6839-51. DOI: 10.1109/TKDE.2022.3192842
[9] Ricciotti W, Cheney J. A Formalization of SQL with Nulls. Journal of Automated Reasoning. 2022 Nov;66(4):989-1030. DOI: https://doi.org/10.1007/s10817-022-09632-4
[10] Me’Ndez M, Merayo MG, Chittayasothorn S. Handling Null Values in SQL Queries on Relational Databases. In2024 10th International Conference on Engineering, Applied Sciences, and Technology (ICEAST) 2024 May 1 (pp. 65-68). IEEE. DOI: 10.1109/ICEAST61342.2024.10553913
[11] Meleková A, Kvet M. Effect of JOIN Type on Query Performance. In2025 37th Conference of Open Innovations Association (FRUCT) 2025 May 14 (pp. 179-184). IEEE. DOI: 10.23919/FRUCT65909.2025.11007985
[12] Salunke SV, Ouda A. A Performance Benchmark for the PostgreSQL and MySQL Databases. Future Internet. 2024;16(10):382. DOI: 10.3390/fi16100382
[13] Llano-Rios TF. Using dynamic schemas for query optimization over JSON data. DOI: https://doi.org/10.18297/etd/4308
[14] Šušter I, Ranisavljević T. Optimization of MySQL database. Journal of process management and new technologies. 2023 Jun 25;11(1-2):141-51. DOI: https://doi.org/10.5937/jpmnt11-44471
[15] Zhang Y, Chronis Y, Patel JM, Rekatsinas T. Simple adaptive query processing vs. learned query optimizers: Observations and analysis. Proceedings of the VLDB Endowment. 2023 Jul 1;16(11):2962-75. DOI: https://doi.org/10.14778/3611479.3611501
[16] Son Y, Kang H, Yeom HY, Han H. A log-structured buffer for database systems using non-volatile memory. In Proceedings of the Symposium on Applied Computing 2017 Apr 3 (pp. 880-886). DOI: https://doi.org/10.1145/3019612.3019675
[17] Kim M, Hwang J, Heo G, Cho S, Mahajan D, Park J. Accelerating String-key Learned Index Structures via Memoization-based Incremental Training. DOI: https://doi.org/10.14778/3659437.3659439
Downloads
Published
Issue
Section
License
Copyright (c) 2025 Adhuresa Hajdini, Lozarta Fazliu, Dea Gjoshi (Author)
This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.
