Analyzing CRUD Performance and Data Integrity Trade-offs in MariaDB: The Impact of Indexes, Foreign Keys, and Referential Actions

Abstract

Data integrity in RDBMS frequently raises concerns regarding computational efficiency, particularly when handling large-scale datasets. This study analyzes the trade-off between execution time and storage overhead in MariaDB (InnoDB storage engine) by evaluating different configurations of indexing, foreign key constraints, and the ON DELETE CASCADE rule. A quantitative benchmarking approach was used through a synthetic dataset consisting of 3,000,000 rows distributed in a one-to-many relationship across 100,000 parent records. Performance evaluation focused on CRUD and JOIN operations, with 30 iterations conducted under each configuration and analyzed using descriptive statistical measures, including mean and standard deviation. The experimental results show that B-Tree indexes drastically accelerate read operations (SELECT and JOIN), with JOIN execution improving by more than 1,400-fold, from an average of 0.8516 s without an index to 0.0006 s with indexing, despite increasing disk storage overhead by 57.59 MB to 72.64 MB. Conversely, for write operations (INSERT and UPDATE), foreign key constraints and the CASCADE rule create additional latency of up to 0.0019 s due to relational integrity validation and real-time lock management within the storage engine layer. The study concludes that enforcing strict referential integrity in RDBMSs results in a performance decline for write operations; therefore, the database schema design must be precisely tailored to the specific characteristics of the application workload. The contribution of this study lies in providing an empirical comparison of the combined effects of B-Tree indexing and referential integrity actions on storage overhead, read performance, and write latency using a homogeneous multi-million-row relational dataset.