We Built a Free MySQL EXPLAIN Analyzer — Here's What It Catches That You're Missing

Most MySQL performance problems hide in plain sight. The query runs, returns results, and nobody looks at the execution plan until the database is on fire at 3 AM.

I've spent years as a DBA consultant staring at EXPLAIN output for clients across the EU and US. The same patterns repeat: a missing composite index turns a 50ms query into a 5-second table scan. A YEAR() function call on an indexed column defeats the index entirely. A three-table JOIN creates a temporary table with thousands of rows because the optimizer picked the wrong driving table.

MySQL's raw EXPLAIN output tells you all of this — if you know where to look. But reading nested tree output or JSON plans isn't intuitive, and the traditional tabular EXPLAIN format hides critical details.

So I built a tool that does the reading for you.

What Is the ReliaDB EXPLAIN Analyzer?

It's a free, browser-based tool that takes your MySQL or MariaDB EXPLAIN output and gives you:

• Interactive tree visualization with color-coded nodes showing where time and rows are spent
• 49 detection rules that flag full scans, filesort operations, temporary tables, bad row estimates, and more
• Smart composite index recommendations — merges WHERE + JOIN + GROUP BY + ORDER BY columns into one optimal index with the correct column order
• Impact simulation showing the structural plan changes each index would produce
• Query rewrite suggestions with executable SQL alternatives
• DDL schema analysis that cross-references your table definitions with the plan

Everything runs in your browser. No backend, no API calls, no data sent anywhere. Your query plans stay on your machine.

Try the EXPLAIN Analyzer →

How to Use It (3 Steps)

1. Run EXPLAIN ANALYZE on your query in MySQL: EXPLAIN ANALYZE SELECT ...
2. Paste the output into the EXPLAIN Output box — paste it exactly as MySQL prints it, including borders and prompts. The tool strips them automatically.
3. Add your SQL query and DDL (optional but recommended) — paste the original SQL in the "SQL Query" tab and your CREATE TABLE statements in the "Table Schema" tab. This unlocks composite index recommendations, query rewrites, and schema analysis.

That's it. The tool parses, visualizes, and analyzes in under a second.

Real Example: Enterprise Feature Usage Report

Every claim in this blog post is verified against a real MySQL 8.0.45 database. I created a fresh SaaS billing schema with 6 tables and 333,000 rows that this tool has never been trained on — then ran these queries and pasted the raw output.

Here's a feature usage report for enterprise tenants. This is the kind of query that runs on a SaaS dashboard and silently gets slower as your data grows:

SELECT t.name, t.plan, ue.feature,
       SUM(ue.quantity) AS total_usage
FROM usage_events ue
JOIN tenants t ON t.id = ue.tenant_id
WHERE ue.recorded_at >= '2026-01-01'
  AND t.plan = 'enterprise'
GROUP BY t.name, t.plan, ue.feature
ORDER BY total_usage DESC

This query joins 150,000 usage events against 5,000 tenants, filters by plan and date, groups by three columns, and sorts. The analyzer scores it 31 out of 100 — here's why:

Three critical issues stacked on top of each other:

1. Full table scan on tenants (5,000 rows) — no index on plan, so MySQL scans every tenant and filters to 833 enterprise ones. That's scanning 6x more rows than needed.

2. Temporary table with 4,672 rows — the GROUP BY on three columns forces MySQL to materialize all joined results before aggregating.

3. Filesort on 4,672 rows — after grouping, MySQL sorts again for the ORDER BY.

What the tool recommends (click the Indexes tab above to see):

The analyzer generates two index recommendations with ready-to-copy DDL:

• tenants(plan, name, id) — a covering index. plan first for the WHERE filter, name for the GROUP BY, and id trailing so MySQL never touches the table data. The impact simulator confirms: eliminates the full scan, enables join reorder (driving from 833 enterprise tenants instead of 5,000), and provides an index-only scan.

• usage_events(recorded_at, tenant_id, feature, quantity) — a composite covering index that merges WHERE + JOIN + GROUP BY + aggregate columns into one index. recorded_at for the date filter, tenant_id for the join, feature for the GROUP BY, and quantity for the SUM — all from the index, no table access needed.

That second recommendation is the kind of insight that separates a real index advisor from one that just says "add an index on recorded_at." The tool understands that the WHERE, JOIN, GROUP BY, and SELECT columns all belong in one carefully ordered composite.

JOIN-Aware Covering Indexes

Most index tools recommend indexes for single tables based on the WHERE clause. They miss that the inner side of a nested loop join benefits enormously from a covering index that includes the join column, filter columns, AND the selected columns.

Here's a support dashboard query that joins tickets to users:

SELECT st.priority, COUNT(*) AS total,
       AVG(TIMESTAMPDIFF(HOUR, st.created_at, st.resolved_at))
         AS avg_resolution_hours
FROM support_tickets st
JOIN users u ON u.id = st.user_id
WHERE u.role = 'admin'
GROUP BY st.priority

MySQL scans all 30,000 support tickets, then for each one looks up the user via PK and checks if they're an admin. Only 5,912 rows survive the filter — MySQL examined 5x more rows than needed.

The tool recommends two indexes that work together:

• users(role) — lets MySQL drive from admin users first (score: high impact)
• support_tickets(user_id, priority) — a JOIN-aware composite. user_id first for the nested loop lookup, priority second for the GROUP BY. This means MySQL can join and group from the index alone.

This is the cross-clause merging intelligence at work. The tool sees that user_id is the JOIN column and priority is the GROUP BY column, and combines them into one composite instead of recommending two separate single-column indexes.

Three-Table LEFT JOIN: Revenue Leakage Detection

Real billing systems need reconciliation queries. This one finds subscriptions where what's been paid doesn't match what's expected — joining tenants, subscriptions, and invoices:

SELECT t.name, t.region, s.plan,
       s.amount AS expected,
       COALESCE(SUM(i.amount), 0) AS actually_paid,
       s.amount - COALESCE(SUM(i.amount), 0) AS gap
FROM tenants t
JOIN subscriptions s ON s.tenant_id = t.id
LEFT JOIN invoices i ON i.subscription_id = s.id
  AND i.status = 'paid'
WHERE s.status = 'active'
GROUP BY t.name, t.region, s.plan, s.amount, s.id
HAVING gap > 10
ORDER BY gap DESC LIMIT 100

This 134ms query creates a temporary table with 5,000 rows and examines 22,629 rows through the LEFT JOIN (7.5 invoice lookups per subscription × 5,000 active subscriptions).

The analyzer generates four recommendations — click the Indexes tab above:

1. subscriptions(status, tenant_id, plan, amount) — WHERE + JOIN + GROUP BY columns merged into one composite. MySQL can filter active subscriptions AND join to tenants AND group, all from the index.

2. invoices(subscription_id, amount) — JOIN-aware covering index. The join column first, then the SUM column as trailing. MySQL reads invoice amounts directly from the index without touching the table.

3. subscriptions(status, plan, amount, tenant_id, id) — a wider covering alternative that eliminates all table lookups for subscriptions.

4. tenants(name, region) — GROUP BY composite for the tenants side.

The tool also flags three query hints: the HAVING clause references a column alias (potential issue), the GROUP BY uses a non-unique name column (could produce unexpected results if tenant names collide), and ORDER BY + LIMIT without a unique tiebreaker means inconsistent pagination.

Query Rewrite Suggestions With Executable SQL

Some performance problems can't be fixed with indexes. The query itself needs to change. Here's a common one — using YEAR() on an indexed date column:

SELECT COUNT(*) FROM invoices WHERE YEAR(due_date) = 2025

The YEAR() function wraps the indexed column due_date, preventing MySQL from using the index for range filtering. MySQL scans all 60,000 rows and applies the function to each one. The analyzer flags this (score: 77) and generates the rewrite:

-- Before (full table scan, 60K rows examined):
WHERE YEAR(due_date) = 2025

-- After (index range scan, only matching rows):
WHERE due_date >= '2025-01-01' AND due_date < '2026-01-01'

The tool detects 7 rewrite patterns total: YEAR() → range, subquery → JOIN, NOT IN → LEFT JOIN, unnecessary GROUP BY fix, SELECT * elimination, OFFSET → keyset pagination, and ORDER BY RAND() alternatives.

49 Rules That Catch What You'd Miss at 3 AM

The analyzer applies 49 detection rules organized by severity:

Each rule explains why it matters and provides a specific recommendation with copyable DDL.

Index Impact Simulator

After getting an index recommendation, the hardest question is: "will this actually help?" The impact simulator answers this with structural predictions based on MySQL optimizer guarantees — not guesswork:

Click the Indexes tab on any embedded analysis, then expand Estimated Impact to see the simulation.

Tested Against AI: Tool vs. Senior DBA Analysis

I compared the analyzer's output against independent senior DBA analysis on 5 queries from a SaaS billing database the tool had never seen. Here's how they compare:

The tool matches a senior DBA's analysis on 90% of findings and beats it on automation: impact simulation, executable rewrites, and automated query pattern detection. The 10% gap is in niche areas like recognizing that ORDER BY DATEDIFF() is monotonically equivalent to ORDER BY date — edge cases that rarely affect real-world optimization.

The full comparison across all 50 test queries is documented in the project repository.

What Makes the Index Advisor Smart

The index advisor isn't a simple "column mentioned in WHERE → add index" engine. It has several intelligence layers that prevent bad recommendations:

• Cross-clause merging — WHERE, JOIN, GROUP BY, ORDER BY, and aggregate columns for the same table get merged into one optimal composite index instead of separate single-column recommendations
• PK-aware — never recommends an index starting with a primary key column (InnoDB's clustered index already covers it) or a composite that duplicates an existing index
• DDL validation — validates recommended columns actually exist in the table schema, filtering out SQL aliases like revenue or total_usage that appear in ORDER BY but aren't real columns
• Subquery isolation — parses only the outer query's GROUP BY/ORDER BY, not inner subquery clauses
• Existing index awareness — checks DDL for indexes that already cover the recommendation and skips them

Works With Whatever MySQL Throws At You

The tool handles every EXPLAIN format MySQL and MariaDB produce:

• EXPLAIN ANALYZE — the tree format with actual execution times (MySQL 8.0.18+)
• EXPLAIN FORMAT=JSON — the detailed JSON output with cost breakdowns
• Traditional EXPLAIN — the classic tabular format with type, key, rows, Extra columns
• MariaDB ANALYZE — table format with r_rows, r_filtered actual metrics
• MariaDB ANALYZE FORMAT=JSON — JSON with r_total_time_ms, filesort nesting

Paste it straight from your terminal — the tool auto-strips mysql> prompts, +---+ borders, | ... | wrappers, continuation lines (-> SELECT, -> GROUP BY), and N rows in set footers. No cleanup needed.

Share and Embed Your Analysis

• Copy Share Link — generates a URL with the full plan, query, and DDL compressed in the hash. Anyone with the link sees exactly what you see.
• Embed — generates an <iframe> snippet you can paste into blog posts, runbooks, Confluence pages, or internal docs. Every embedded analysis in this blog post is a live instance of the tool — you can interact with them.

Your Data Stays Private

This tool runs 100% in your browser. There's no backend, no API calls, no analytics on your queries, no database that stores your plans. The source code is TypeScript rules — not a language model, not an AI API call.

Query plans often contain table names, column names, and row counts that reveal your application's data model. That information shouldn't leave your machine just to visualize an EXPLAIN plan.

Now Open Source

The EXPLAIN Analyzer is now open source under the MIT license. You can browse the full source code, run it locally, or contribute:

View on GitHub →

What's in the repo:

• Complete Vue 3 + TypeScript source code
• All 49 detection rules, index advisor, query rewriter, and impact simulator
• All parsers (MySQL EXPLAIN ANALYZE tree, FORMAT=JSON, traditional table, MariaDB ANALYZE)
• Test suite with 50 queries against a 680K-row MySQL 8.0 database
• Test database setup scripts so you can reproduce every result

Why open source? We built this tool because no equivalent existed for MySQL. The PostgreSQL community has explain.dalibo.com and PEV2 — MySQL had nothing comparable. Making it open source means the MySQL community can use it, improve it, and trust that their query plans aren't being sent to any server.

The tool is also listed on awesome-mysql — the curated list of open-source MySQL tools.

You can run it locally in three commands:

git clone https://github.com/Mughees52/mysql-explain-analyzer.git
cd mysql-explain-analyzer
npm install && npm run dev

Or just use the hosted version at reliadb.com/tools/explain/ — same code, zero setup.

Try It Now

Open the EXPLAIN Analyzer, paste your EXPLAIN ANALYZE output, and see what it catches. Add your SQL query and table DDL for the full analysis — composite index recommendations, impact simulation, query rewrites, and schema checks.

If you're dealing with a performance problem that needs a deeper look, book a free assessment and I'll review your slowest queries with you.

Open the EXPLAIN Analyzer →

Frequently Asked Questions

Does the tool cost anything?

No. The EXPLAIN Analyzer is completely free with no usage limits, no sign-up, and no premium tier. It runs as a client-side application in your browser — there's no server cost to offset.

Does it work with MariaDB?

Yes. The tool supports MariaDB ANALYZE table format (with r_rows, r_filtered) and ANALYZE FORMAT=JSON (with r_total_time_ms). It auto-detects whether the input is MySQL or MariaDB and applies engine-specific rules — including MariaDB-specific patterns like rowid filtering, FirstMatch semi-joins, and hash joins.

Is my data safe?

Your data never leaves your browser. There's no backend server, no API calls, no telemetry. You can verify this in your browser's Network tab — the only requests are for static assets.

Is this using AI to analyze queries?

No. The analysis engine uses 49 hand-written detection rules, a query-aware index advisor, and a structural impact simulator — all deterministic TypeScript code. It was tested against Claude AI analysis on 50 real queries from a database it had never seen, and it matches or exceeds the AI's recommendations while being faster, deterministic, and completely private.

Can I embed the analysis in my documentation?

Yes. Click the Embed button in the Actions section after analyzing a plan. It generates an <iframe> code snippet with your analysis pre-loaded. The embedded view shows a clean, focused analysis with a "Powered by ReliaDB" footer.