SQL Optimization in e-Commerce Platforms Using Large Language Models: A Multi-Pattern Query Empirical Study

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.