SQL Optimization in e-Commerce Platforms Using Large Language Models: A Multi-Pattern Query Empirical Study
Article Sidebar
Main Article Content
Abstract
The rapid expansion in e-Commerce domains has presented an unprecedented challenge to database systems that support the execution of complex analytic queries on multi-billion transaction data stream while keeping queries’ response latency under one second. It is commonly observed that Large Language Models (LLMs) are very good at processing natural language queries, rather than struggling with syntax or semantics. This feature is instrumental to various tasks like analytics acceleration, transaction speed-up and handling large workloads across different domains. In this paper, a detailed examination of how LLMs can be employed as intelligent assistants for Structure Query Language (SQL) query optimization throughout eight different types of query patterns is performed (with two test cases each, resulting in 16 test cases). An in-depth experimental study is carried out among three main database systems (PostgreSQL 16, MariaDB ColumnStore, and SQLite) with relevant real-world e-Commerce datasets such as Brazilian e-Commerce Public Dataset and Synthetic e-Commerce Relational Dataset ranging from 10 million to 1 billion records. Our methodology of sixteen test-cases includes temporal aggregations, multi-dimensional grouping, ranking queries, window functions, complex joins, full-text search, recursive queries and geospatial analysis. Results demonstrate that implementing the LLM's recommended query, indexing, and schema-level changes yielded performance improvements ranging from 43% to 94% across test cases, whereby temporal aggregations yield 89% average improvement while multi-dimensional grouping nets 86% improvement on the whole system.
Article Details
This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.
All articles published in JIWE are licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International (CC BY-NC-ND 4.0) License. Readers are allowed to
- Share — copy and redistribute the material in any medium or format under the following conditions:
- Attribution — You must give appropriate credit, provide a link to the license, and indicate if changes were made. You may do so in any reasonable manner, but not in any way that suggests the licensor endorses you or your use;
- NonCommercial — You may not use the material for commercial purposes;
- NoDerivatives — If you remix, transform, or build upon the material, you may not distribute the modified material.
References
[1] R. Magesh, U. Ilakkiyaa, R. Shanthini, and R. Charanya, “Unlocking the Potential of Data Lakes: Organizing and Storing Marketing Data for Analysis,” in Data Engineering for Data-driven Marketing, Emerald Publishing Limited, 2025, pp. 199–216. doi: 10.1108/978-1-83662-326-720251030.
[2] H. B. Abdalla, Y. Kumar, Y. Zhao, and D. Tosi, “A Comprehensive Survey of MapReduce Models for Processing Big Data,” Big Data and Cognitive Computing, vol. 9, no. 4, p. 77, Mar. 2025, doi: 10.3390/bdcc9040077.
[3] S.-C. Haw, K.-W. Ng, J. J, and P. Naveen, “OD-SIF: An Ontology-Driven Schema Integration Framework for e-Commerce Platform,” in 2024 7th Asia Conference on Cognitive Engineering and Intelligent lnteraction (CEII), IEEE, Dec. 2024, pp. 196–201. doi: 10.1109/CEII65291.2024.00046.
[4] N. Hettiarachchi and P. Yapa, “Advancements in SQL Query Optimization: A Review of Join Order and Index Selection,” in 2025 5th International Conference on Machine Learning and Intelligent Systems Engineering (MLISE), IEEE, Jun. 2025, pp. 31–39. doi: 10.1109/MLISE66443.2025.11100192.
[5] S. Zinchenko and D. Ponomaryov, “The Selection Problem in Multi-Query Optimization: a Comprehensive Survey,” Jan. 2025.
[6] Y. Tian, “Query Optimization in the Wild: Realities and Trends,” Oct. 2025.
[7] B. B. Duddeti, A. K. Naskar, V. P. Meena, J. Bahadur, and I. A. Hameed, “Constrained search space selection based optimization approach for enhanced reduced order approximation of interconnected power system models,” Sci. Rep., vol. 15, no. 1, p. 7999, Mar. 2025, doi: 10.1038/s41598-025-91913-0.
[8] G. Yunfan, S. Tao, X. Youlin, X. Yun, W. Haofen, and Z. Jiawei, “Chat-REC: Towards Interactive and Explainable LLMs-Augmented Recommender System,” 2023.
[9] H. Wang et al., “Generative Large Recommendation Models: Emerging Trends in LLMs for Recommendation,” in WWW Companion 2025 - Companion Proceedings of the ACM Web Conference 2025, 2025. doi: 10.1145/3701716.3715865.
[10] P. G. Selinger, M. M. Astrahan, D. D. Chamberlin, R. A. Lorie, and T. G. Price, “Access path selection in a relational database management system,” in Proceedings of the 1979 ACM SIGMOD international conference on Management of data - SIGMOD ’79, New York, New York, USA: ACM Press, 1979, p. 23. doi: 10.1145/582095.582099.
[11] N. Mazumdar and P. K. D. Sarma, “Sequential pattern mining algorithms and their applications: a technical review,” Int. J. Data Sci. Anal., vol. 20, no. 3, pp. 1683–1726, Sep. 2025, doi: 10.1007/s41060-024-00659-x.
[12] J. Sabonsolin and D. Lao, “A comprehensive systematic literature review of multiple sequence alignment algorithms,” Discover Computing, vol. 29, no. 1, p. 33, Jan. 2026, doi: 10.1007/s10791-026-09911-3.
[13] J. Gao, Z. Li, and W. Liu, “A Strategy of Efficient and Accurate Cardinality Estimation Based on Query Result,” Xibei Gongye Daxue Xuebao/Journal of Northwestern Polytechnical University, vol. 36, no. 4, pp. 768–777, Aug. 2018, doi: 10.1051/jnwpu/20183640768.
[14] X. Zhang and A. Eldawy, “Spatial Query Optimization With Learning,” Proceedings of the VLDB Endowment, vol. 17, no. 12, pp. 4245–4248, Aug. 2024, doi: 10.14778/3685800.3685846.
[15] A. Kamal, V. Kantere, and C. Zuzarte, “Robust Query Optimization in the Era of Machine Learning: State-of-the-Art and Future Directions,” in 2024 IEEE 40th International Conference on Data Engineering (ICDE), IEEE, 2024, pp. 5371–5375.
[16] M. Mozaffari, A. Dignös, J. Gamper, and U. Störl, “Self-tuning Database Systems: A Systematic Literature Review of Automatic Database Schema Design and Tuning,” ACM Comput. Surv., vol. 56, no. 11, pp. 1–37, Nov. 2024, doi: 10.1145/3665323.
[17] Z. Guan, “Advances and Challenges in Machine Learning-Based Cardinality Estimation for Database Query Optimization,” ITM Web of Conferences, vol. 78, p. 03023, Sep. 2025, doi: 10.1051/itmconf/20257803023.
[18] M. F. B. Harunasir, N. Palanichamy, S.-C. Haw, and K.-W. Ng, “Sentiment analysis of Amazon product reviews by supervised machine learning models,” Journal of Advances in Information Technology, vol. 14, no. 4, pp. 857–862, Jan. 2023, doi: 10.12720/jait.14.4.857-862.
[19] K. Kim, J. Jung, I. Seo, W.-S. Han, K. Choi, and J. Chong, “Learned Cardinality Estimation: An In-depth Study,” in Proceedings of the 2022 International Conference on Management of Data, New York, NY, USA: ACM, Jun. 2022, pp. 1214–1227. doi: 10.1145/3514221.3526154.
[20] R. Marcus et al., “Neo,” Proceedings of the VLDB Endowment, vol. 12, no. 11, pp. 1705–1718, Jul. 2019, doi: 10.14778/3342263.3342644.
[21] X. Yu, G. Li, C. Chai, and N. Tang, “Reinforcement Learning with Tree-LSTM for Join Order Selection,” in 2020 IEEE 36th International Conference on Data Engineering (ICDE), IEEE, Apr. 2020, pp. 1297–1308. doi: 10.1109/ICDE48307.2020.00116.
[22] J. Ding et al., “ALEX: An Updatable Adaptive Learned Index,” in Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data, New York, NY, USA: ACM, Jun. 2020, pp. 969–984. doi: 10.1145/3318464.3389711.
[23] Z. Ye et al., “Deep Learning Workload Scheduling in GPU Datacenters: A Survey,” ACM Comput. Surv., vol. 56, no. 6, pp. 1–38, Jun. 2024, doi: 10.1145/3638757.
[24] C.-X. Ling, K.-W. Ng, and H. A. Santoso, “Mapping Relational Database to Full-Text XML for Open Journal System Cross-Platform Article Distribution,” Journal of Informatics and Web Engineering, vol. 4, no. 3, pp. 259–277, Oct. 2025, doi: 10.33093/jiwe.2025.4.3.16.
[25] J. Li et al., “Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs,” Nov. 2023.
[26] Z. Hong et al., “Next-Generation Database Interfaces: A Survey of LLM-Based Text-to-SQL,” IEEE Trans. Knowl. Data Eng., vol. 37, no. 12, pp. 7328–7345, Dec. 2025, doi: 10.1109/TKDE.2025.3609486.
[27] T. Scholak, N. Schucher, and D. Bahdanau, “PICARD: Parsing Incrementally for Constrained Auto-Regressive Decoding from Language Models,” Sep. 2021.
[28] M. Pourreza and D. Rafiei, “DIN-SQL: Decomposed In-Context Learning of Text-to-SQL with Self-Correction,” Nov. 2023.
[29] X. Zhou, Z. Sun, and G. Li, “DB-GPT: Large Language Model Meets Database,” Data Science and Engineering, vol. 9, pp. 102–111, Jan. 2024, doi: 10.1007/s41019-023-00235-6.
[30] I. Trummer, “CodexDB: Synthesizing Code for Query Processing from Natural Language Instructions using GPT-3 Codex,” Proceedings of the VLDB Endowment, vol. 15, no. 11, pp. 2921–2928, Jul. 2022, doi: 10.14778/3551793.3551841.
[31] H. Li et al., “LLMs-as-Judges: A Comprehensive Survey on LLM-based Evaluation Methods,” Dec. 2024.
[32] M. Cheng et al., “A Survey on Table Mining with Large Language Models: Challenges, Advancements and Prospects,” Apr. 01, 2025. doi: 10.36227/techrxiv.174352282.22844759/v1.
[33] L. Shi, Z. Tang, N. Zhang, X. Zhang, and Z. Yang, “A Survey on Employing Large Language Models for Text-to-SQL Tasks,” ACM Comput. Surv., vol. 58, no. 2, pp. 1–37, Jan. 2026, doi: 10.1145/3737873.
[34] X. Liu et al., “A Survey of Text-to-SQL in the Era of LLMs: Where Are We, and Where Are We Going?,” IEEE Trans. Knowl. Data Eng., vol. 37, no. 10, pp. 5735–5754, Oct. 2025, doi: 10.1109/TKDE.2025.3592032.
[35] L. D. Krisnawati, A. W. Mahastama, S.-C. Haw, K.-W. Ng, and P. Naveen, “Indonesian-English Textual Similarity Detection Using Universal Sentence Encoder (USE) and Facebook AI Similarity Search (FAISS)”, CommIT (Communication and Information Technology) Journal, vol. 18, no. 2, pp. 183–195, Sep. 2024. https://doi.org/10.21512/commit.v18i2.11274
[36] B. Hilprecht, A. Schmidt, M. Kulessa, A. Molina, K. Kersting, and C. Binnig, “DeepDB: learn from data, not from queries!,” Proceedings of the VLDB Endowment, vol. 13, no. 7, pp. 992–1005, Mar. 2020, doi: 10.14778/3384345.3384349.
[37] Brazilian e-Commerce Public Dataset. https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce
[38] Synthetic e-Commerce Relational Dataset. https://www.kaggle.com/datasets/naelaqel/synthetic-e-Commerce-relational-dataset