Analyzing Database Index Strategies with Claude: EXPLAIN, Composite Order

1. Why EXPLAIN plans are hard to read despite being precise

An EXPLAIN plan does not give the query optimizer's opinion, it gives a concrete description of how the database would actually execute a query: which access type gets used, how many rows are estimated, which indexes were considered or discarded. The problem is not the plan's data quality, it is its density: terms like ref, range, index_merge, or Using filesort are hard to place without experience, and the relevant rows easily get lost in a long output.

Claude is well suited to translating a concrete EXPLAIN output line by line and highlighting the decisive signals, for example that a given query shows type: ALL, meaning a full table scan, even though a matching index exists. What matters is presenting Claude with the complete plan including the estimated row count, not just an excerpt, since the assessment would otherwise happen without the relevant numbers.

-- Example: EXPLAIN output for a query with a missing index
EXPLAIN SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' AND o.created_at > '2026-08-01';

-- Output excerpt:
-- table: o | type: ALL | key: NULL | rows: 184320 | Extra: Using where
-- table: c | type: eq_ref | key: PRIMARY | rows: 1

-- type: ALL means a full table scan over 184,320 rows, even though
-- status + created_at would be a good composite index candidate

2. Working through EXPLAIN plans with Claude systematically

A useful prompting approach is not asking Claude for a general assessment, but pointing specifically at the critical fields: access type per table, index used, estimated row count, and whether a temporary table or a filesort is needed. Claude can walk through these fields systematically and name, with priority, which table in a join causes the largest share of the cost, which is not always immediately obvious with multiple joins in a complex query.

A pattern that works well in practice is a two-step prompt: first, ask Claude for a plain description of what the plan says, without optimization suggestions yet, to make sure the interpretation is correct. Only in the second step comes the request for concrete index suggestions, based on the already validated interpretation from the first answer.

# Two-step prompt for Claude Code: interpret first, optimize second
claude "Here is an EXPLAIN output (explain.txt) for a slow query.
Step 1: Describe ONLY what the plan says, per table: access type, index
used, estimated row count, filesort/temp table if present. No
optimization suggestions yet.

3. Systematically finding missing and redundant indexes

Missing indexes usually show up clearly in the EXPLAIN plan as a full table scan for a query with a selective WHERE condition. Redundant indexes, by contrast, are invisible in the plan of a single query and can only be spotted by comparing all defined indexes on a table, for example when an index on (status) is already covered by a composite index (status, created_at) and therefore becomes superfluous. Claude can systematically check a table's full index list for such overlaps.

Redundant indexes are not merely a cosmetic issue, they cost extra write load on every INSERT and UPDATE, since each index has to be maintained separately, without providing any additional benefit on reads. A prompt that gives Claude a table's complete index definition and asks specifically about prefix overlaps reliably surfaces such cases, whereas they often stay unnoticed for years in a running system.

-- Example: redundant index Claude should catch during review
SHOW INDEX FROM orders;
-- idx_status              (status)
-- idx_status_created       (status, created_at)
-- idx_customer             (customer_id)

-- idx_status is redundant: any query that would use idx_status
-- can also use the prefix of idx_status_created -> idx_status
-- can be dropped without losing read performance

4. Composite index column order: selectivity over convenience

For a composite index spanning multiple columns, the column order decisively determines how usable the index actually is. As a rule of thumb, highly selective columns, meaning columns with many distinct values, should come before less selective columns, and equality conditions should come before range conditions, because the index can no longer be used in sorted order after the first range condition. An index (created_at, status) is considerably less usable for a query with status = 'pending' AND created_at > X than (status, created_at).

Claude can apply these ordering rules to a concrete query and explain why one column order fits better than another, when given the typical WHERE clauses of a table's most important queries. What matters is not optimizing a single query in isolation, but finding an index that serves as many of the most frequent query patterns well as possible, since every additional index generates write load.

# Prompt for Claude Code: justify composite index column order
claude "Here are the 5 most frequent queries against the orders table
(queries.sql) with their daily execution frequency. Propose one or two
composite indexes that serve as many of these queries well as possible.
Explicitly justify the column order based on:
- Selectivity (number of distinct values)
- Equality before range conditions
- Sort requirements (ORDER BY) the index could also cover

5. Covering indexes and the extra fields worth checking

An often-overlooked optimization opportunity shows up in the Extra field of an EXPLAIN plan: Using index means the query can be served entirely from the index, without looking up the actual table row, a so-called covering index. If this hint is missing despite a matching index existing, it is usually because one queried column is not included in the index and therefore an additional row lookup becomes necessary.

Claude can check whether an existing index can be turned into a covering index by adding one or two additional columns, while at the same time pointing out the cost of that extension, such as larger index size and more write overhead. This trade-off between read performance and write cost is a classic one that Claude can name in a structured way, but cannot conclusively decide for the specific application.

6. The central limit: Claude does not know the real data distribution

The most important caveat for every index recommendation from Claude is that it does not know the actual data distribution in the production database, unless concrete statistics are supplied. An index on a column with only two possible values, such as a boolean flag, brings almost no benefit, even if it looks plausible on paper, because the optimizer often still chooses a full table scan given such low selectivity. Without the actual cardinality, Claude can only suspect this case, not know it for certain.

Every index suggestion from Claude should therefore be cross-checked against the real SHOW INDEX and cardinality data of the affected table before being implemented. A prompt that explicitly includes the results of ANALYZE TABLE or a count of distinct values per column leads to noticeably more reliable recommendations than a prompt containing only the schema without actual distribution data.

-- Determining real cardinality before an index recommendation
SELECT
  COUNT(*) AS total,
  COUNT(DISTINCT status) AS distinct_status,
  COUNT(DISTINCT customer_id) AS distinct_customer
FROM orders;
-- total: 2400000 | distinct_status: 5 | distinct_customer: 48000
-- status has LOW selectivity (only 5 values) -> barely effective as
-- the sole index column, but good as the first column combined with
-- a more selective range condition such as created_at

7. Index maintenance: keeping statistics current

Even a correctly chosen index can lose its effectiveness when the statistics used by the optimizer go stale. After large data imports or bulk deletes, the actual cardinalities often diverge noticeably from the most recently collected statistics, which can cause the optimizer to keep choosing a full table scan despite a matching index existing, because the estimated costs are based on outdated numbers. Claude can point out during review when an ANALYZE TABLE or the equivalent of the database system in use makes sense after major data changes.

An additional, often neglected aspect is index fragmentation on tables with very high write load, which can degrade the actual read performance of an otherwise correctly chosen index over time. Claude can assess, based on the described write load and maintenance routines, whether a regular rebuild process for affected indexes makes sense, though the concrete decision depends on the specific database engine and its particular maintenance tooling.

8. A review workflow: proposal, verification, measurement

A three-step workflow works well in practice. First, Claude works out an index proposal with reasoning based on the EXPLAIN plan, schema, and cardinality data. Second, this proposal gets tested in a staging environment with as realistic a copy of production data as possible, comparing the EXPLAIN output before and after the index change. Third, the actual query time gets measured under realistic load, not just a look at the estimated row count in the plan.

This workflow prevents the most common mistake with AI-assisted index recommendations: implementing a plausible-sounding suggestion directly in production without verifying it against real data. Especially on very large tables, an additional index can have negative effects even when it improves the read speed of a single query, for example when the extra write load noticeably slows down other, more frequent write operations.

9. Further limits beyond the data distribution

Besides not knowing the real data distribution, Claude also does not know the actual hardware configuration of the database, such as the RAM available for the buffer pool, or the specific version of the database system in use with its particular optimizer quirks. An index suggestion that makes sense for MySQL 8 does not necessarily fit equally well for PostgreSQL, because optimizer strategies and support for certain index types such as partial or functional indexes differ.

That is why the database system and its version in use should always be stated explicitly in the prompt to Claude, instead of expecting a generic SQL answer that silently assumes a particular system. The final responsibility for verifying every suggestion against real data and under realistic load remains with the development team in every case.

10. Summary

11. FAQ: Database Index Strategies with Claude: Key Questions