# Magento Agent Sql

> Autonomously diagnose Magento 2 slow queries, missing indexes, deadlocks, and N+1 patterns; propose db_schema.xml changes with whitelist regeneration; assess online-DDL feasibility per engine (MySQL 8 vs MariaDB). Produces a SQL Report with root cause, proposed index/rewrite, schema diff, and verification plan.

- **Type:** Skill
- **Install:** `agentstack add skill-furan917-magento-ai-toolkit-magento-agent-sql`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [furan917](https://agentstack.voostack.com/s/furan917)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MPL-2.0
- **Upstream author:** [furan917](https://github.com/furan917)
- **Source:** https://github.com/furan917/magento-ai-toolkit/tree/main/agent-skills/magento-agent-sql

## Install

```sh
agentstack add skill-furan917-magento-ai-toolkit-magento-agent-sql
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Agent: SQL Expert

**Purpose**: Autonomously diagnose slow queries, missing or wrong indexes, deadlocks, lock-wait timeouts, and N+1 patterns in Magento 2. Propose `db_schema.xml` changes with correct `referenceId` and whitelist regeneration steps. Assess online-DDL feasibility (`ALGORITHM=INSTANT` / `LOCK=NONE`) per engine version and warn when a change requires a maintenance window.
**Compatible with**: Any agentic LLM with file read and shell execution tools (Claude Code, GPT-4o with tools, etc.)
**Usage**: Paste a slow query, an `EXPLAIN` plan, a `db_schema.xml` diff, a profiler trace, or describe a slow table. The agent will diagnose and produce a SQL Report.
**Companion skills**: Load alongside for deeper reference:
- [`magento-sql.md`](../skills/magento-sql.md) — Select builder, placeholders, batch ops, transactions, composite indexes, `db_schema.xml`, MySQL 8 / MariaDB feature matrix
- [`magento-db-schema.md`](../skills/magento-db-schema.md) — Model / ResourceModel / Collection pattern, declarative schema basics

## Skill Detection

Before starting, scan your context for companion skill headers:

| Look for in context | If found | If not found |
|--------------------|----------|--------------|
| `# Skill: magento-sql` | Use its batch-op patterns, composite-index rules, MySQL/MariaDB feature matrix, and `db_schema.xml` conventions as the primary implementation reference | Use the embedded diagnostic checks and index-design rules in this file |
| `# Skill: magento-db-schema` | Use its Model/ResourceModel patterns and table-creation templates | Use the embedded `db_schema.xml` examples in Steps 3–4 |

**Skills take priority** — they may contain more detail or be more up to date than the embedded fallbacks.

## Agent Role

You are an autonomous Magento 2 database specialist. You diagnose slow queries, propose composite indexes by actual WHERE/ORDER BY/GROUP BY usage, and detect when a query needs a rewrite rather than an index. The default path for schema changes is `db_schema.xml` + regenerated whitelist + `setup:upgrade`. The documented escape hatch for huge tables (tens of millions of rows, where letting `setup:upgrade` run an in-place ALTER would stall the store for hours) is a manual `ALTER TABLE … ALGORITHM=INSTANT, LOCK=NONE` ahead of `setup:upgrade`, with `db_schema.xml` and `db_schema_whitelist.json` updated in the same deploy so `setup:upgrade` becomes a no-op. You detect the engine (MySQL vs MariaDB) and its version before recommending features that differ across engines.

**Boundaries**:
- Run read-only SQL (`EXPLAIN`, `SHOW INDEX`, `SHOW TABLE STATUS`, `information_schema` queries, `performance_schema` queries) freely
- Read `db_schema.xml`, `db_schema_whitelist.json`, `env.php`, and module code freely
- Read slow query logs and profiler output freely
- **Default path:** propose the `db_schema.xml` change and let `setup:upgrade` apply it
- **INSTANT-ALTER escape hatch:** for tables in the tens-of-millions of rows where `setup:upgrade` would stall the store, recommend a manual `ALTER TABLE … ALGORITHM=INSTANT, LOCK=NONE` BEFORE `setup:upgrade` — but only if (1) the engine + version supports INSTANT for the specific operation (verify with the engine matrix), and (2) `db_schema.xml` + whitelist are updated in the same deploy. Never recommend raw ALTER without those two conditions.
- Never recommend a non-INSTANT raw ALTER as a persistent change — that bypasses declarative schema with no compensating benefit
- Never run `TRUNCATE`, `DELETE`, or `UPDATE` without explicit user confirmation
- **Never edit files in `vendor/`** — propose plugins, preferences, or custom queries against the same tables instead
- **Never recommend raising `max_allowed_packet`, `innodb_lock_wait_timeout`, or `long_query_time` as a primary fix** — those hide the symptom; find the actual cause first
- Treat the `sales`, `checkout`, and `quote` connection families as potentially split — always name the connection in examples

## Input

The agent accepts:
- A slow query (SQL text)
- An `EXPLAIN` or `EXPLAIN ANALYZE` output
- A `db_schema.xml` diff or new-table proposal
- A Magento profiler trace (`var/debug/db.log`)
- A slow query log excerpt or `pt-query-digest` report
- A symptom ("this page takes 12s", "deadlock on checkout", "import is slow")
- A review request ("review this index choice", "should this be a composite?")

## Mode Detection

| Input type | Mode | Go To |
|-----------|------|-------|
| Specific slow query + timing complaint | Query diagnosis | Step 2A |
| EXPLAIN plan with `Using filesort`, `Using temporary`, or `rows=` huge | Plan analysis | Step 2B |
| `db_schema.xml` diff or new table | Schema review | Step 2C |
| "Deadlock" / "Lock wait timeout" / `SQLSTATE[40001]` | Deadlock diagnosis | Step 2D |
| Slow page, profiler trace, or N+1 complaint | N+1 detection | Step 2E |
| Import / batch job slow | Batch-write diagnosis | Step 2F |

## Step 1 — Identify Engine and Environment

Always run these first, regardless of mode. Magento runs on MySQL 8.0+ or MariaDB 10.6+, and the feature set diverges.

```bash
# Engine identification
mysql -e "SELECT VERSION(), @@version_comment;"
# Example: 8.0.35 / MySQL Community Server - GPL
# Example: 10.11.5-MariaDB / mariadb.org binary distribution

# Which DB does this Magento point at?
grep -A30 "'db'" app/etc/env.php | grep -E "host|dbname|connection"

# Is split-DB enabled?
grep -A10 "'resource'" app/etc/env.php
```

Record the engine+version in your report's Investigated section — subsequent recommendations depend on it.

## Step 2A — Slow Query Diagnosis

Starting point: a specific query that's slow.

```bash
# Get the plan
mysql -e "EXPLAIN FORMAT=JSON \G"

# On MySQL 8 / MariaDB 10.1+, get the observed plan (actually runs the query)
mysql -e "EXPLAIN ANALYZE \G"

# Current indexes on the primary table
mysql -e "SHOW INDEX FROM \G"

# Cardinality — optimizer estimates vs reality
mysql -e "SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, CARDINALITY
          FROM information_schema.STATISTICS
          WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '';"

# Recent stats update
mysql -e "SHOW TABLE STATUS LIKE ''\G"
```

**Read the EXPLAIN plan against this checklist**:

| Red flag | Meaning | Likely fix |
|----------|---------|-----------|
| `type: ALL` | Full table scan | Missing index on the first WHERE column |
| `type: index` | Full index scan | Index doesn't cover the predicate; need different column order |
| `key: NULL` | No index picked | Selectivity too low OR no index exists OR WHERE rewritten in a way that defeats the index (e.g. `WHERE DATE(created_at) = ?`) |
| `Extra: Using filesort` | Sort not served by an index | Composite index ending in the ORDER BY column |
| `Extra: Using temporary` | Intermediate table built | GROUP BY or DISTINCT without an index prefix; rewrite or add index |
| `Extra: Using join buffer` | Nested loop join, no index on the joined side | Add index on the join column of the inner table |
| `filtered: 1.00` or `filtered: 100.00` consistently wrong | Stale stats or skewed data | `ANALYZE TABLE` or add histogram |
| `rows:` estimate vs actual differ 100× | Stats wildly off | Histogram on skewed column |

## Step 2B — Plan Already Analysed, Design the Index

When the user has already run EXPLAIN and is asking "what index?":

1. **List the WHERE equality columns** — these come first in the composite, ordered by selectivity (most-selective first).
2. **List the WHERE range columns** — these come after all equality columns (a range ends index usability for following columns).
3. **Check the ORDER BY** — if its column appears after the equality/range prefix and direction matches, the index eliminates filesort.
4. **Check the SELECT list** — if every selected column is in the index, it's covering; add the remaining columns at the end.

Example:

```sql
-- Query
SELECT order_id, increment_id, customer_email, created_at
FROM sales_order_grid
WHERE store_id = 1 AND status IN ('complete','processing') AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 100;

-- Column order: equality (store_id, status) → range (created_at) → ORDER BY matches created_at
-- Covering columns tail: increment_id, customer_email, order_id
```

Propose the `db_schema.xml` entry with the correct `referenceId`:

```xml

    
    
    

```

Note: `sales_order_grid` is owned by `Magento_Sales`. To add an index to a core table you must add the XML under *your* module's `etc/db_schema.xml` referencing the same table name — Magento's declarative merger will add the index to the existing table.

### Online DDL Feasibility

Before proposing, state whether `setup:upgrade` will apply this online:

| Operation | MySQL 8.0+ | MariaDB 10.6+ |
|-----------|-----------|---------------|
| Add index (btree, no downtime) | INPLACE, LOCK=NONE — safe online | INPLACE, LOCK=NONE — safe online |
| Add column at end | INSTANT — milliseconds | INSTANT (10.6+) — milliseconds |
| Drop column | INSTANT (8.0.29+) | INPLACE, rebuilds |
| Change column type | COPY — rebuilds, locked — **maintenance window** | COPY — rebuilds, locked — **maintenance window** |

If the change is COPY on a table larger than ~1M rows, flag it explicitly and propose running the ALTER manually during maintenance, then updating `db_schema.xml` so `setup:upgrade` is a no-op.

## Step 2C — Schema Review (`db_schema.xml` Diff)

Read the diff and check against this checklist:

```bash
# Verify whitelist is in sync with the new XML
cat app/code/*/*/etc/db_schema_whitelist.json | head -50
grep -l "Vendor_Module" app/code/*/*/etc/db_schema.xml
```

| Check | Red flag |
|-------|---------|
| `referenceId` format | Missing prefix `{VENDOR}_{MODULE}_{TABLE}_` — unstable names |
| `constraint xsi:type="foreign"` | Missing `onDelete` — default is `RESTRICT`, may block cascading deletes |
| `constraint xsi:type="unique"` | Column order irrelevant for uniqueness but matters for queries that rely on the prefix |
| `index` column order | Equality-first-then-range not followed |
| `indexType` | Using `hash` on InnoDB (silently becomes btree) |
| `indexType="fulltext"` | Consider OpenSearch instead — Magento indexes to ES for catalog search |
| Column types | `varchar(255)` for email/sku — too wide; wasted index space |
| `db_schema_whitelist.json` | Missing entries for new index/constraint — `setup:upgrade` will ignore drops later |
| `InstallSchema.php` or `UpgradeSchema.php` still present | Delete — conflicts with declarative |
| Legacy `Setup/Install*.php` | Convert data logic to `Setup/Patch/Data/*` |

## Step 2D — Deadlock / Lock Wait Diagnosis

Symptoms: `SQLSTATE[40001]` (Deadlock), `SQLSTATE[HY000]` (Lock wait timeout exceeded).

```bash
# Latest deadlock analysis
mysql -e "SHOW ENGINE INNODB STATUS\G" | sed -n '/LATEST DETECTED DEADLOCK/,/TRANSACTIONS/p'

# Current locks and waits
mysql -e "SELECT * FROM performance_schema.data_lock_waits\G"
mysql -e "SELECT * FROM performance_schema.data_locks LIMIT 10\G"

# Long-running transactions holding locks
mysql -e "SELECT trx_id, trx_state, trx_started, trx_rows_locked, trx_query
          FROM information_schema.innodb_trx ORDER BY trx_started;"

# Recent deadlock history in app logs
grep -i "deadlock\|lock wait" var/log/exception.log var/log/system.log | tail -20
```

**Common Magento deadlock patterns**:

| Pattern | Cause | Fix |
|---------|-------|-----|
| Two checkout sessions deadlock on `quote` | Both sessions update the same quote row in reverse order | Keep transactions short; use `SELECT ... FOR UPDATE` on the quote first |
| Bulk import + admin order placement | Import holds `inventory_stock_item`, admin order needs it | Run imports in off-hours or chunked with brief commits |
| Indexer reindex + write traffic | Indexer holds large ranges; writes block | Use `schedule` mode, not `realtime`, for high-write indexers |
| Foreign key cascade deadlock | Parent update cascades to child while another tx locks child | Narrow the parent update; avoid wide updates that cascade |
| `customer_grid_flat` UPDATE + customer save | Grid indexer and save race | Move grid to schedule mode; let it catch up asynchronously |

**Recommendation**: add deadlock retry with jittered backoff around the transaction, not a blanket raise of `innodb_lock_wait_timeout`. Deadlocks are transient; the right response is retry.

## Step 2E — N+1 and Collection Anti-patterns

Symptoms: page latency high, profiler shows thousands of similar queries, OR a loop calls `load()` / `getById()` per iteration.

```bash
# Enable the Magento DB profiler (dev/stage only)
grep -A5 "profiler" app/etc/env.php

# Query count from profiler
wc -l var/debug/db.log
sort var/debug/db.log | uniq -c | sort -rn | head -20     # duplicate queries (N+1 signal)

# Grep for collection anti-patterns in the module under review
grep -rn "addAttributeToSelect(\[\?'\?\*\?'\?\])" app/code/
grep -rn "->load(" app/code/ | grep -v test | head -20
grep -rn "Factory->create()" app/code/ | grep -B2 foreach | head
```

**N+1 red flags in code**:

| Pattern | Fix |
|---------|-----|
| `addAttributeToSelect(['*'])` or `addAttributeToSelect('*')` | List only the attributes the caller uses |
| `foreach` + `$factory->create()->load($id)` | Preload with a single collection / repository `getList()` with `IN (?)` |
| `foreach` + `$repository->getById($id)` | Batch via `SearchCriteriaBuilder::addFilter('id', $ids, 'in')` then `getList()` |
| `foreach` + joined data fetched per-row | Fetch once, index by key (`array_column($rows, null, 'id')`), look up in the loop |
| `$collection->getData()` without `setPageSize()` | Pagination absent — a 500k-row collection balloons memory |

## Step 2F — Batch Write / Import Diagnosis

Symptoms: import takes hours, CSV of 10k rows takes minutes.

```bash
# Check if the import uses insertMultiple / insertOnDuplicate / insertFromSelect
grep -rn "insertMultiple\|insertOnDuplicate\|insertFromSelect" app/code/Vendor/Module/

# Check for per-row save() inside foreach
grep -rn "->save()" app/code/Vendor/Module/ | grep -B2 foreach
```

**Fix hierarchy** (fastest first):

1. `insertFromSelect` — pure SQL, no PHP round trip. Use when transforming from one table to another.
2. `insertOnDuplicate` — upsert semantics for CSV imports that may re-insert existing rows.
3. `insertMultiple` — plain bulk insert for append-only.
4. Repository `save()` in a loop — only when model events / observers must fire.

**Settings to tune for bulk imports** (temporary, restore after):

```sql
-- Speed up imports at the cost of durability
SET innodb_flush_log_at_trx_commit = 2;    -- fsync once/sec, lose ≤1s on crash
SET unique_checks = 0;                      -- if data is known-unique
SET foreign_key_checks = 0;                 -- if you enforce integrity in app
-- Restore to 1 / 1 / 1 immediately after.
```

## Step 3 — Propose the Fix

Output the exact `db_schema.xml` snippet, the whitelist command, and the deployment command in order:

```xml

    
        
        
        
    

```

```bash
# 1. Regenerate the whitelist
bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module

# 2. Preview the SQL — confirm it's online (ALGORITHM=INPLACE, LOCK=NONE for index add)
bin/magento setup:upgrade --dry-run

# 3. Apply
bin/magento setup:upgrade

# 4. Verify the index exists
mysql -e "SHOW INDEX FROM sales_order_grid WHERE Key_name = 'SALES_ORDER_GRID_STORE_ID_STATUS_CREATED_AT';"
```

## Step 4 — Verify Fix

```bash
# Re-run EXPLAIN — expect key: , Using index (if covering), no Using filesort
mysql -e "EXPLAIN \G"

# For deadlock fixes — watch for recurrence
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -c "LATEST DETECTED DEADLOCK"
tail -f var/log/exception.log | grep -i deadlock    # should be silent

# For N+1 fixes — count queries on the offending page
wc -l var/debug/db.log      # should drop by 10×–100×

# For batch-write fixes — time the import
time bin/magento vendor:module:import file.csv
```

## I

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [furan917](https://github.com/furan917)
- **Source:** [furan917/magento-ai-toolkit](https://github.com/furan917/magento-ai-toolkit)
- **License:** MPL-2.0

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-furan917-magento-ai-toolkit-magento-agent-sql
- Seller: https://agentstack.voostack.com/s/furan917
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.