EUVIMEDEUVIMED entdecken
Crossref

QUERY OPTIMIZATION ON SEMI STRUCTURED MYSQL RETAIL DATA USING GENERATED COLUMNS

Adi Handika, Muqtafiy Muhammad, Muhammad Azka Bani Shalih, Nabil Yudha Syahputra, Imam Prayogo Pujiono

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.

AbstractBibliografische AngabenZitierenVerwandte Studien

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}
}
Kostenlos starten →