Impact of Join Order and Main Memory Buffer Size in Query Optimization with Materialization
DOI:
https://doi.org/10.3126/jist.v30i2.84384Keywords:
Left-deep tree, Join cardinality, Join selectivity, MaterializationAbstract
Query optimization is one of the crucial steps during query processing in any database management system that determines the best way to run a high-level query, such as SQL (Structured Query Language). To optimize any query, the query optimization process choses the most efficient execution plan from multiple execution plans. For a given high level query, the query optimization process selects an efficient execution plan to enhance performance of the database. Out of different factors that influence query optimization, the most crucial ones are join order and the use of main memory buffer and this paper focuses on the impact of these two factors in query optimization. In this paper, we consider all left deep tree join orders of relations and cost of each join order is calculated using different main memory buffer sizes with materialization. The cost of join-orders is calculated by considering number of secondary memory blocks accessed during query execution. The result of intermediate join operation is also materialized and the cost of the join orders is calculated. This study highlights that different join orders with different join selectivity and different main memory buffer sizes have significant impact on query optimization. The query with the value of join selectivity less than one and larger main memory buffer reduces the number of secondary memory blocks accessed and hence reduces the cost of join operation. The cost of join queries can further be reduced significantly by using indexes.
Downloads
References
Azhir, E., Navimipour, N. J., Hosseinzadeh, M., Sharifi, A., Unal, M., & Darwesh, A. (2022). Join queries optimization in the distributed databases using a hybrid multi-objective algorithm. Cluster Computing, 25(3). https://doi.org/10.1007/s10586-021-03451-9
Chen, L., Huang, H., & Chen, D. (2021). Join cardinality estimation by combining operator-level deep neural networks. Information Sciences, 546. https://doi.org/10.1016/j.ins.2020.09.065
Divya, V. L., Job, P. A., & Mathew, P. K. (2024). Secure query processing and optimization in cloud environment: a review. In Information Security Journal (Vol. 33, Issue 2). https://doi.org/10.1080/19393555.2023.2270976
Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.
Graefe, G. (1993). Query Evaluation Techniques for Large Databases. ACM Computing Surveys (CSUR), 25(2). https://doi.org/10.1145/152610.152611
Janjua, J. I., Khan, T. A., Zulfiqar, S., & Usman, M. Q. (2022). An Architecture of MySQL Storage Engines to Increase the Resource Utilization. 2022 International Balkan Conference on Communications and Networking, BalkanCom 2022. https://doi.org/10.1109/BalkanCom55633.2022.9900616
Jarke, M., & Koch, J. (1984). Query Optimization in Database Systems. ACM Computing Surveys (CSUR), 16(2). https://doi.org/10.1145/356924.356928
Ji, L., Zhao, R., Dang, Y., Liu, J., & Zhang, H. (2023). Query Join Order Optimization Method Based on Dynamic Double Deep Q-Network. Electronics (Switzerland), 12(6). https://doi.org/10.3390/electronics12061504
Krogh, J. W. (2018). MySQL Connector/Python Revealed: SQL and NoSQL Data Storage Using MySQL for Python Programmers. In MySQL Connector/Python Revealed: SQL and NoSQL Data Storage Using MySQL for Python Programmers. https://doi.org/10.1007/978-1-4842-3694-9
Mor, J., Kashyap, I., & K. Rathy, R. (2012). Analysis of Query Optimization Techniques in Databases. International Journal of Computer Applications, 47(15). https://doi.org/10.5120/7262-0127
MySQL AB. (2019). MySQL :: Other MySQL Documentation. https://dev.mysql.com/doc/index-other.html
Paudel, N., & Bhatta, J. (2019). Cost-Based Query Optimization in Centralized Relational Databases. Journal of Institute of Science and Technology, 24(1). https://doi.org/10.3126/jist.v24i1.24627
Qian, W., Jing, Y., Wang, X., & Wu, Z. (2022). Cardinality Estimation Method for Multitable JOIN Query Optimization. Jisuanji Gongcheng/Computer Engineering, 48(6). https://doi.org/10.19678/j.issn.1000-3428.0061625
Repas, D., Luo, Z., Schoemans, M., & Sakr, M. (2023). Selectivity Estimation of Inequality Joins in Databases. Mathematics, 11(6). https://doi.org/10.3390/math11061383
Schuh, S., Chen, X., & Dittrich, J. (2016). An experimental comparison of thirteen relational equi-joins in main memory. Proceedings of the ACM SIGMOD International Conference on Management of Data, 26-June-2016. https://doi.org/10.1145/2882903.2882917
Swami, A., & Gupta, A. (1988). Optimization of large join queries. Proceedings of the 1988 ACM SIGMOD International Conference on Management of Data, 8–17. https://doi.org/10.1145/50202.50203
Wu, Z., Negi, P., Alizadeh, M., Kraska, T., & Madden, S. (2023). FactorJoin: A New Cardinality Estimation Framework for Join Queries. Proceedings of the ACM on Management of Data, 1(1). https://doi.org/10.1145/3588721
Downloads
Published
How to Cite
Issue
Section
License
Copyright (c) 2025 Institute of Science and Technology, T.U.
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.
The views and interpretations in this journal are those of the author(s). They are not attributable to the Institute of Science and Technology, T.U. and do not imply the expression of any opinion concerning the legal status of any country, territory, city, area of its authorities, or concerning the delimitation of its frontiers of boundaries.
The copyright of the articles is held by the Institute of Science and Technology, T.U.
