QUERY OPTIMIZATION ON SEMI STRUCTURED MYSQL RETAIL DATA USING GENERATED COLUMNS
DOI:
https://doi.org/10.36595/misi.v9i2.2058Keywords:
MySQL 8.0, Data Semi Terstruktur, Generated Column, Indeks B-Tree, Performa KueriAbstract
This study compares query performance among three data storage models in MySQL: the relational model (retail_relational), the unoptimized JSON model (retail_json_raw), and the JSON model with generated columns and indexes (retail_json_gc). The dataset used is a public retail transaction dataset containing more than one million records with eight attributes including Invoice, StockCode, Description, Quantity, InvoiceDate, Price, Customer ID, and Country. Four query scenarios were tested: filtering by country, searching by stock code, filtering by price range, and grouping by country for revenue aggregation. Each query was executed ten times and analyzed using EXPLAIN to observe the execution plan. Results show that the JSON_GC model consistently achieves the fastest execution time across all scenarios. For the stock code filter query, JSON_GC achieves an average of 0.006 seconds compared to 1.577 seconds for the relational model and 1.950 seconds for JSON_Raw. For the GROUP BY aggregation query, JSON_GC requires only 0.002 seconds compared to more than 1.6 seconds for both other models. This performance difference is due to generated columns supporting index scans and covering indexes, preventing MySQL from performing full table scans. The JSON_Raw model is consistently the slowest due to the overhead of JSON_EXTRACT functions and the inability to be directly indexed. This study concludes that generated columns are a highly effective optimization strategy for managing semi structured JSON based data in MySQL.
Downloads
References
[1] M. Lutfi et al., Konsep Dasar Analisis dan Perancangan Sistem Informasi. PT Penerbit Qriset Indonesia, 2025.
[2] A. Satria, Z. Zulham, and S. Salamah, “Optimalisasi Transformasi Data Semi-Terstruktur dan Tidak Terstruktur Berbasis XML/JSON dalam Arsitektur Data Warehouse Modern,” Djtechno J. Teknol. Inf., vol. 6, no. 2, pp. 820–835, 2025.
[3] B. Suriansyah, L. F. Mz, A. I. Rachman, and G. Pratiwi, “Rekontruksi Arsitektur DataBase untuk Peningkatan Proses Load Data,” J. Media Inform., vol. 6, no. 2, pp. 1455–1460, 2025.
[4] A. Amalia and M. A. Rojabi, MEMBANGUN API MODERN DENGAN JSON. Afdan Rojabi Publihser, 2026.
[5] A. Turmudi, F. Lanang, M. R. A. Pratama, F. N. Ihsan, D. A. Nugroho, and I. P. Pujiono, “Analisis Perbandingan Struktur Data Dan Kompleksitas Koding Antara MySQL Dan MongoDB Pada Pengembangan Aplikasi Blog,” J. Sains Inform. Terap., vol. 5, no. 1, pp. 10–17, 2026.
[6] D. A. E. Saputri, P. D. Lestari, and S. Mukaromah, “Analisis Pengaruh Implementasi Index B-Tree terhadap Kinerja Waktu Database,” in Prosiding Seminar Nasional Teknologi dan Sistem Informasi, 2023, pp. 475–481.
[7] D. A. Saputra, M. A. Farich, M. N. Anugerah, I. P. Pujiono, and D. A. Nugroho, “Perbandingan Kinerja MongoDB dan MySQL pada Aplikasi dengan Beban Data Besar,” SAINSTECH J. Penelit. DAN Pengkaj. SAINS DAN Teknol., vol. 35, no. 4, pp. 71–78, 2025.
[8] M. N. S. Yusfa, A. Musyawwa, and A. B. Pratama, “Analisis Penerapan Indexing dan Query Optimization pada Database MySQL untuk Meningkatkan Performa Aplikasi Absensi Mahasiswa Universitas Pamulang,” J. Inf. Syst. Bus. Technol., vol. 1, no. 4, pp. 9–14, 2025.
[9] E. Wihardjo, “DENGAN SOFTWARE MODERN BAR•,” Manaj. Data Dengan Softw. Mod., vol. 37, 2025.
[10] D. Van Landuyt, M. Levrau, V. Reniers, and W. Joosen, “An e-commerce benchmark for evaluating performance trade-offs in document stores,” in International Conference on Big Data Analytics and Knowledge Discovery, Springer, 2024, pp. 284–290.
[11] A. Suciani, D. Ruhiat, and S. D. Rahayu, “Komparasi hasil analisis beda rata-rata menggunakan metode statistik parametrik dan nonparametrik,” JRMST| J. Ris. Mat. Dan Sains Terap., vol. 2, no. 2, 2022.
[12] U. Firdaus, A. S. Rini, A. A. Ghifari, A. A. D. Saputra, M. N. Syahwaludin, and D. R. Aliandy, “ANALISIS DAN OPTIMASI PADA DATABASE RELASIONAL MENGGUNAKAN INDEKS DAN NORMALISASI TINGKAT LANJUT: STUDI KASUS SISTEM INFORMASI AKADEMIK,” SIGARUDA J. J. Ilm. Bid. Sos. Ekon. Budaya, Teknol. dan Pendidik., vol. 1, no. 2, pp. 566–571, 2025.
[13] A. K. Wijaya, “Analisis Struktur Basis Data pada Aplikasi E-Commerce Tokopedia: Studi Kualitatif terhadap Desain dan Optimalisasi Skema Relasional,” SAINSTECH J. Penelit. DAN Pengkaj. SAINS DAN Teknol., vol. 35, no. 2, pp. 50–57, 2025.
[14] A. Fadli, M. I. Zulfa, A. W. W. Nugraha, A. Taryana, and M. S. Aliim, “Analisis perbandingan unjuk kerja database sql dan database nosql untuk mendukung era big data,” J. Nas. Tek. Elektro, pp. 154–158, 2020.
[15] H. Haerullah, “PERBANDINGAN EFISIENSI QUERY DATABASE RELASIONAL DAN NOSQL PADA APLIKASI E-COMMERCE: STUDI EKSPERIMEN,” J. Rekayasa Teknol. Inf., vol. 9, no. 4, pp. 386–395, 2025.
[16] M. N. Ilham, A. F. Setiawan, I. Prawira, D. Purnomo, D. A. Nugroho, and I. P. Pujiono, “EVALUASI PERFORMA MYSQL DAN MONGODB PADA QUERY PATTERN E-COMMERCE SKALA KECIL BERBASIS LITERATUR,” JATI (Jurnal Mhs. Tek. Inform., vol. 10, no. 1, pp. 15–21, 2026.
[17] T. R. Hadyan and S. Wahyu, “OPTIMALISASI KINERJA QUERY DAN BACKEND SISTEM PELAPORAN PENUNJANG MEDIS PADA SISTEM INFORMASI MANAJEMEN RUMAH SAKIT,” JATI (Jurnal Mhs. Tek. Inform., vol. 10, no. 2, pp. 3210–3220, 2026.
[18] H. Utomo, “Perbandingan tabel mortalita indonesia dan tabel mortalita cso menggunakan uji mann-whitney dan uji kruskal-wallis,” Syntax Lit. J. Ilm. Indones., vol. 6, no. 3, p. 1210, 2021.
[19] P. Kosamkar, G. Sharma, L. Upadhyaya, B. Shah, and S. Patel, “Query Optimization: Techniques and Strategies for MySQL Performance Improvement,” in International Conference on ICT for Sustainable Development, Springer, 2025, pp. 134–142.
[20] T. F. Llano-Rios, “Using dynamic schemas for query optimization over JSON data.,” 2024.
Downloads
Published
How to Cite
Issue
Section
License
Semua tulisan pada jurnal ini menjadi tanggungjawab penuh penulis.
