Evaluasi Pengaruh Desain Indeks Komposit terhadap Waktu Respon Query SELECT pada MySQL InnoDB

Abstract

Query performance optimization has become a critical requirement in modern information system development, particularly in environments that handle large volumes of data and intensive transactional workloads. One commonly recommended optimization technique is the use of composite indexes; however, their effectiveness strongly depends on the alignment between index design and the executed query patterns. This study aims to evaluate the impact of composite index design on SELECT query response time in MySQL InnoDB using a quasi-experimental approach. Four treatments were examined: no index, single-column index, Composite Index A, and Composite Index B. Data were collected using MySQL Performance Schema, Slow Query Log, and Python-based benchmarking scripts. The results demonstrate that well-designed composite indexes significantly reduce query response time compared to other treatments. Composite Index B achieved the fastest average response time (70 ms), consistent with the leftmost prefix rule, which emphasizes the importance of column order in index design. This study concludes that an appropriate composite index design contributes significantly to improving SELECT query efficiency, particularly in collaborative information systems involving high query activity from multiple actors such as lecturers, administrative staff, and other system users.