Article Per Year (5 Year)
p-Index From 2021 - 2026
0.23
P-Index
This Author published in this journals
All Journal Jurnal Manajemen Informatika dan Sistem Informasi
Muqtafiy Muhammad
Universitas Islam Negeri K.H. Abdurrahman Wahid Pekalongan
Computer Science & IT

Published : 1 Documents Claim Missing Document
Claim Missing Document
Check
Articles

Found 1 Documents
Search

 1 
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 Vol. 9 No. 2 (2026): MISI Juni 2026
Publisher : LPPM STMIK Lombok

Show Abstract | Download Original | Original Source | Check in Google Scholar | DOI: 10.36595/misi.v9i2.2058

Abstract

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.
Co-Authors Adi Handika Imam Prayogo Pujiono Muhammad Azka Bani Shalih Nabil Yudha Syahputra