AI-assisted SQL optimisation – LLM + EXPLAIN + Blackfire, 5x speedup

SQL optimisation has always required human expertise, but the workflow has changed. I now use an LLM as a pair programmer when reading EXPLAIN output – it translates the cryptic rows into plain English and suggests indexes. Blackfire shows me where the time goes. Together they cut a query from 4.2 seconds to 0.8 seconds in an afternoon. I show the complete workflow with real examples.

The workflow

# Step 1: Find slow queries with Blackfire
blackfire curl https://shop.ddev.site/catalog/category/view/id/5

# In Blackfire dashboard:
# SQL tab -> sort by "Total Time" -> find the outliers
# Our problem: "SELECT ... FROM sales_order_item JOIN ..." taking 4.2s

-- Step 2: Run EXPLAIN on the slow query
EXPLAIN SELECT
    soi.sku,
    SUM(soi.qty_ordered) as total_qty,
    SUM(soi.row_total) as total_revenue
FROM sales_order_item soi
JOIN sales_order so ON so.entity_id = soi.order_id
WHERE so.created_at >= '2024-01-01'
  AND so.status = 'complete'
  AND soi.product_type = 'simple'
GROUP BY soi.sku
ORDER BY total_revenue DESC
LIMIT 100;

-- EXPLAIN output (the cryptic part):
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | so    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1284736 |     1.11 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | soi   | NULL       | ref  | FK_ORDER_ITEM  | ...  | 4       | so.  | 3       |    10.00 | Using where                                  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+

AI-assisted EXPLAIN interpretation

Prompt to Claude:
"Explain this MySQL EXPLAIN output for a Magento order analysis query.
What are the performance problems and what indexes should I add?

[paste EXPLAIN output above]

Tables involved:
- sales_order: ~1.3M rows, columns: entity_id, created_at, status, ...
- sales_order_item: ~4.1M rows, columns: item_id, order_id, sku, product_type, ..."

Claude's response (summarised):
"The main problem is in the sales_order table:
- type: ALL = full table scan of 1.3M rows
- key: NULL = no index being used
- Extra: 'Using temporary; Using filesort' = creating temp table and sorting

The WHERE clause filters on (created_at, status) but there's no index covering both.
MySQL is scanning all 1.3M rows to find orders matching the date/status filter.

Recommended indexes:
1. Composite index on sales_order(status, created_at) - covers the WHERE clause
2. If you frequently filter by date ranges: consider (created_at, status) order
3. The sales_order_item side looks fine (using existing FK index)

Expected improvement: type changes from ALL to range, rows examined drops
from 1.3M to ~50K for a 90-day window with 'complete' status."

Adding the index and verifying

<?php

// Adding index via Magento Schema Patch
namespace Vendor\Module\Setup\Patch\Schema;

class AddOrderAnalyticsIndex implements \Magento\Framework\Setup\Patch\SchemaPatchInterface
{
    public function __construct(
        private \Magento\Framework\Setup\SchemaSetupInterface $setup
    ) {}

    public function apply(): static
    {
        $this->setup->startSetup();
        $conn  = $this->setup->getConnection();
        $table = $this->setup->getTable('sales_order');

        // Check if index already exists
        $indexes = $conn->getIndexList($table);
        if (!array_key_exists('IDX_SALES_ORDER_STATUS_CREATED_AT', $indexes)) {
            $conn->addIndex(
                $table,
                'IDX_SALES_ORDER_STATUS_CREATED_AT',
                ['status', 'created_at'],
                \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_INDEX
            );
        }

        $this->setup->endSetup();
        return $this;
    }

    public static function getDependencies(): array { return []; }
    public function getAliases(): array { return []; }
}

-- After adding index - verify with EXPLAIN
EXPLAIN SELECT ... (same query);

-- New EXPLAIN output:
-- type: range (was ALL)
-- key: IDX_SALES_ORDER_STATUS_CREATED_AT
-- rows: 48291 (was 1284736 - 96% fewer rows examined)
-- Extra: Using index condition; Using temporary; Using filesort
--        (temporary+filesort remains but on 48K rows, not 1.3M)

Results

# Before optimisation:
blackfire curl https://shop.ddev.site/admin/reports/bestsellers/
# SQL time: 4.2s (this one query)
# Total request: 6.8s

# After adding composite index:
blackfire curl https://shop.ddev.site/admin/reports/bestsellers/
# SQL time: 0.8s
# Total request: 2.1s
# 5x improvement on this query, 3x on total request time

# Follow-up: add covering index for GROUP BY (eliminates filesort)
# ALTER TABLE sales_order_item ADD INDEX IDX_SKU_ANALYTICS (product_type, sku, order_id);
# SQL time: 0.3s (another 2.7x improvement)

The AI-assisted optimisation workflow

1. Blackfire – identify which queries are slow and how much time they take
2. EXPLAIN – run on the slow query, save the output
3. AI (Claude) – paste EXPLAIN + table schema, ask “what’s wrong and what index should I add”
4. Verify – add the suggested index in a schema patch, re-run EXPLAIN, check rows examined
5. Blackfire again – profile the request to confirm end-to-end improvement

Summary

AI does not replace knowing SQL and index fundamentals – it accelerates the diagnostic step. Reading EXPLAIN output fluently takes years of experience; getting a plain-English translation of “type: ALL, Using temporary, Using filesort” instantly removes that barrier. The AI is a knowledgeable pair programmer who never gets tired of explaining MySQL internals. The human still makes the decision about which index to actually add and verifies it with EXPLAIN and Blackfire.