(untitled) (Journal of Institute of Science and Technology, 2025)
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.