QUERY OPTIMIZATION ON SEMI STRUCTURED MYSQL RETAIL DATA USING GENERATED COLUMNS
Jurnal Manajemen Informatika dan Sistem Informasi · 09.06.2026 · 9 (2) · 248-259
DOI: 10.36595/misi.v9i2.2058 ↗Kurz gefasst
Fachartikel in der Zeitschrift Jurnal Manajemen Informatika dan Sistem Informasi, online erschienen am 09.06.2026. Verfasst von Adi Handika und 4 weiteren Autor:innen.
Abstract (Originalsprache)
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.
Zitieren
APA
Handika, A., Muhammad, M., Shalih, M. A. B., Syahputra, N. Y., & Pujiono, I. P. (2026). QUERY OPTIMIZATION ON SEMI STRUCTURED MYSQL RETAIL DATA USING GENERATED COLUMNS. Jurnal Manajemen Informatika dan Sistem Informasi, 9(2), 248-259. https://doi.org/10.36595/misi.v9i2.2058
Vancouver
Handika A, Muhammad M, Shalih MAB, Syahputra NY, Pujiono IP. QUERY OPTIMIZATION ON SEMI STRUCTURED MYSQL RETAIL DATA USING GENERATED COLUMNS. Jurnal Manajemen Informatika dan Sistem Informasi. 2026;9(2):248-259. doi:10.36595/misi.v9i2.2058
BibTeX
@article{Handika2026,
author = {Handika, Adi and Muhammad, Muqtafiy and Shalih, Muhammad Azka Bani and Syahputra, Nabil Yudha and Pujiono, Imam Prayogo},
title = {QUERY OPTIMIZATION ON SEMI STRUCTURED MYSQL RETAIL DATA USING GENERATED COLUMNS},
journal = {Jurnal Manajemen Informatika dan Sistem Informasi},
year = {2026},
volume = {9},
number = {2},
pages = {248--259},
doi = {10.36595/misi.v9i2.2058}
}
EUVIMED