TaurusDB AI Slow SQL Diagnosis: LLM-Assisted Tuning Cuts Query 90% in TPC-H Test
TaurusDB's AI_ANALYZE_SLOW_SQL function automatically collects execution plans, optimizer traces, and schema statistics, sends them to a large language model via MaaS, and returns a diagnostic report with index recommendations; a TPC-H Q14 test on TaurusDB MySQL 2.0.81 showed a covering index reduced query latency from 2.2s to 0.2s (90% improvement).
Diagnostic Principle: Database Provides Context, LLM Analyzes
TaurusDB's AI_ANALYZE_SLOW_SQL function automates slow-query diagnosis by gathering the query text, execution plan, optimizer trace, table and index metadata, and statistics, then forwarding this context to a Model-as-a-Service (MaaS) large language model. The model returns a structured report containing a diagnostic conclusion, supporting evidence, optimization suggestions, risk warnings, and known limitations. Users invoke the function via a simple SELECT statement and then verify and apply the recommendations manually.
Diagnostic Information Categories
The diagnostic context comprises three main categories:
Execution plan and optimizer trace – the estimated plan chosen by the optimizer and the detailed trace showing why each access path was selected.
Table and index metadata – schema definitions, column types, index columns, and index cardinality.
Statistics – table row counts (which may be estimates), index cardinality, and histogram information.
Data Privacy and Execution Model
Only the SQL text, table/column/index names, execution plan, optimizer trace, and a minimized metadata summary are sent to the MaaS endpoint. Data from tables not involved in the query is never transmitted. The diagnosis uses the estimated execution plan; the target query is not executed to measure latency, and indexes are not created automatically. All suggestions require manual verification and implementation.
Function Interface: AI_ANALYZE_SLOW_SQL
The function accepts three required parameters and an optional options JSON object:
AI_ANALYZE_SLOW_SQL(model_name, sql_text, database_name [, options])Prerequisites: the instance must have MaaS enabled, and the calling account needs model-invocation permission plus access to the referenced tables. The function must be called via a standalone SELECT outside any explicit transaction.
Case Study: TPC-H Q14 Promotion Revenue Query
Environment and Data
The test used the standard TPC-H data model at scale factor SF=1, generated with tpchgen-cli 4.0.0. Eight tables totaled ~1.10 GB of raw text (additional space for indexes). The database was a Huawei Cloud TaurusDB MySQL instance (version 2.0.81.260900). The business query calculates the percentage of revenue from promotional products shipped in September 1995 (TPC-H Q14).
Original Query and Execution Plan
The query joins lineitem and part on l_partkey = p_partkey, filters on l_shipdate between 1995-09-01 and 1995-10-01, and aggregates promotional vs. total revenue. Before optimization, the optimizer chose a full table scan on lineitem (6,001,215 rows) followed by a date filter that retained only 75,983 rows (1.27%). No existing index started with l_shipdate, so the scan read the entire table.
USE sales_analytics;
SET @slow_sql = 'SELECT
100.00 * sum(
CASE WHEN p_type LIKE 'PROMO%' THEN
l_extendedprice * (1 - l_discount)
ELSE
0
END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue
FROM
lineitem,
part
WHERE
l_partkey = p_partkey
AND l_shipdate >= date '1995-09-01'
AND l_shipdate < CAST('1995-10-01' AS date)';SELECT AI_ANALYZE_SLOW_SQL(
'huawei/deepseek-v4.1-flash',
@slow_sql,
'sales_analytics') AS diagnostic_report;Model Diagnosis and Index Recommendation
The model identified that the lineitem access type was ALL (full scan) and that no index led with l_shipdate. It recommended evaluating a covering index on lineitem with l_shipdate as the leading column, optionally including l_partkey, l_extendedprice, and l_discount to avoid table lookups, while noting the trade-off of increased index size and write overhead.
Evaluate adding an index on lineitem led by l_shipdate . If the query is frequent and the date range narrow, consider a covering index such as (l_shipdate, l_partkey, l_extendedprice, l_discount) to reduce full-table scans and lookups; but assess index size and write overhead.
Index Creation and Optimized Plan
Following the suggestion, the following covering index was created in a test environment:
CREATE INDEX ix_lineitem_shipdate_cover
ON lineitem(l_shipdate, l_partkey, l_extendedprice, l_discount);The optimized EXPLAIN FORMAT=TREE output shows an index range scan on lineitem using the new covering index, with estimated cost dropping from 839,965 to 83,069 and estimated rows from 659,804 to 149,806. The part table continues to use a primary-key lookup.
-> Aggregate: sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))), sum((case when (part.p_type like 'PROMO%') then (lineitem.l_extendedprice * (1 - lineitem.l_discount)) else 0 end))
-> Nested loop inner join (cost=83069.08 rows=149806)
-> Index range scan on lineitem using ix_lineitem_shipdate_cover (cost=30636.98 rows=149806)
-> Single-row index lookup on part using PRIMARY (p_partkey=lineitem.l_partkey) (cost=0.25 rows=1)Performance Results
Performance was measured with one warm-up run followed by five alternating executions; the median latency is reported. After adding the index, query latency decreased by approximately 90%, from 2.2 seconds to 0.2 seconds. The business result remained identical: promo_revenue = 16.3807786264 (≈16.38%).
The article cautions that the index consumes storage and adds write-maintenance cost, so production adoption should be evaluated against the actual workload. The observed speedup is specific to this query and data distribution.
Conclusion and Future Work
TaurusDB automates the collection of diagnostic materials (execution plan, optimizer trace, indexes, statistics) and delegates analysis to a large model, allowing users to obtain tuning advice via a single SQL call. The case study demonstrates the workflow: model proposes a covering index, the user validates it through execution, and a 90% latency reduction is achieved. Future directions include extending AI-assisted diagnosis to lock-wait and blocking analysis, system load analysis, and correlation of transaction waits with CPU/I/O metrics.
Appendix: Parameter Tuning and Diagnostic Report Example
The optional options parameter can adjust model timeout and output token limit. Example with a 3-minute timeout and 4096-token cap:
SELECT AI_ANALYZE_SLOW_SQL(
'huawei/deepseek-v4.1-flash',
@slow_sql,
'sales_analytics',
JSON_OBJECT('timeout_ms', 180000, 'max_output_tokens', 4096))
AS diagnostic_report;A sample diagnostic report structure (summarized, not verbatim) includes:
Diagnostic Conclusion: lineitem uses a full table scan filtered by ship date. Recommend evaluating a covering index led by ship date to reduce irrelevant row reads. Key Evidence: lineitem access type is ALL; part joins via primary key; no existing detail index starts with l_shipdate . Possible Cause: Ship-date predicate lacks a matching index entry, preventing direct location of the target month's data. Optimization Suggestion: Create index ix_lineitem_shipdate_cover on lineitem(l_shipdate, l_partkey, l_extendedprice, l_discount) to seek by ship date and cover all required detail columns. Keep the CASE promotional condition unchanged to preserve the denominator. Change Risk: New index increases space and write-maintenance cost; evaluate against business load. Diagnostic Limitation: Report is based on current statistics and estimated plan, not actual runtime; effectiveness must be verified by execution.
To run the original business query after defining @slow_sql:
USE sales_analytics;
PREPARE q14_stmt FROM @slow_sql;
EXECUTE q14_stmt;
DEALLOCATE PREPARE q14_stmt;Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
