Designing Summary Tables to Optimize Performance in Relational Databases: An Empirical Approach

Abstract

The performance of transactional applications involving databases will decline over time due to the increasing amount of stored data and the continuously rising volume of transactions. This research proposes a summary table design method that integrates table partitioning techniques and data aggregation to improve query efficiency without sacrificing the consistency and accuracy of the stored data. We tested this method using 30,275 transaction records on Google Cloud infrastructure and MySQL Server 8.0, demonstrating a data processing speed increase of up to 92.36% compared to conventional table designs. The research results show that this method not only accelerates data processing but also simplifies data management in relational database management systems (RDBMS). This method is relevant for transactional database applications with high transaction loads, such as the financial and e-commerce sectors. By combining the physical table design architecture and efficient query processing, this research contributes to the development of more scalable, reliable, and suitable database designs for high workload scenarios.