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. In: Proceedings of the IEEE High Performance Extreme Computing Conference (HPEC). IEEE; 2016. p. 1-9. Available from: https://doi.org/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. 2023;35(7):6839-6851. Available from: https://doi.org/10.1109/TKDE.2022.3192842
[3] MySQL AB. MySQL Reference Manual 8.0. Oracle Corporation; 2023. Available from: https://dev.mysql.com/doc/refman/8.0/en/
[4] Krogh JW. MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds. Berkeley, CA: Apress; 2020. Available from: https://doi.org/10.1007/978-1-4842-5584-1
[5] Krogh SW. MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds. O'Reilly Media. Available from: https://www.oreilly.com/library/view/mysql-8-query/9781484255841/
[6] Al Saedi AK, Deris MB. An efficient multi join query optimization for DBMS using swarm intelligent approach. In: Proceedings of the 4th World Congress on Information and Communication Technologies (WICT). IEEE; 2014. p. 113-117. Available from: https://doi.org/10.1109/WICT.2014.7077312
[7] Krogh JW. The query optimizer. In: MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds. Berkeley, CA: Apress; 2020. p. 417-485. Available from: https://doi.org/10.1007/978-1-4842-5584-1_17
[8] Leis V, Radke B, Gubichev A, Mirchev A, Boncz P, Kemper A, Neumann T. Query optimization through the looking glass, and what we found running the join order benchmark. The VLDB Journal. 2018 Oct;27(5):643-668. Available from: https://doi.org/10.1007/s00778-017-0480-7
[9] Ricciotti W, Cheney J. A formalization of SQL with nulls. Journal of Automated Reasoning. 2022;66(4):989-1030. Available from: https://doi.org/10.1007/s10817-022-09632-4
[10] Méndez M, Merayo MG, Chittayasothorn S. Handling null values in SQL queries on relational databases. In: Proceedings of the 10th International Conference on Engineering, Applied Sciences, and Technology (ICEAST). IEEE; 2024. p. 65-68. Available from: https://doi.org/10.1109/ICEAST61342.2024.10553913
[11] Meleková A, Kvet M. Effect of JOIN type on query performance. In: Proceedings of the 37th Conference of Open Innovations Association (FRUCT). IEEE; 2025. p. 179-184. Available from: https://doi.org/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. Available from: https://doi.org/10.3390/fi16100382
[13] Llano-Rios TF. Using dynamic schemas for query optimization over JSON data [dissertation]. Available from: 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;11(1-2):141-151. Available from: 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;16(11):2962-2975. Available from: 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 ACM Symposium on Applied Computing. ACM; 2017. p. 880-886. Available from: 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. Proceedings of the VLDB Endowment. 2024. Available from: 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.
The Journal of Computing and Data Technology publishes all articles under the Creative Commons Attribution–NonCommercial 4.0 International License (CC BY-NC 4.0). This license permits others to copy, distribute, reproduce, remix, adapt, and build upon the published work for non-commercial purposes, provided appropriate credit is given to the original authors and the source. By publishing in the Journal of Computing and Data Technology, all authors agree to these licensing terms as a condition of publication.
