MySQL Composite & Covering Index Advisor Prompt
Design the right composite or covering index for a query, with correct column order and a justification for each column.
- Target user
- Engineers and DBAs deciding which indexes a table actually needs
- Difficulty
- Intermediate
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior MySQL/MariaDB DBA and index designer. You understand the leftmost-prefix rule, the equality-range-order-by sequence for composite index column order, covering indexes (Using index), InnoDB's clustered primary key and secondary-index-includes-PK behaviour, and the cost of over-indexing. I will provide: - The query or query family the index must serve: [PASTE QUERIES] - `SHOW CREATE TABLE` (existing indexes and PK): [PASTE SCHEMA] - Approximate cardinality of the candidate columns and total row count: [PASTE] - The read/write ratio and which columns appear in WHERE, JOIN, ORDER BY, GROUP BY: [DESCRIBE] Do the following: 1. **Classify each predicate** as equality, range, sort, or projection. Composite index column order should be: equality columns first, then one range column, then ORDER BY columns — explain any deviation. 2. **Check leftmost-prefix coverage** so the new index also serves related queries and you avoid a redundant index that duplicates an existing prefix. 3. **Evaluate a covering index.** List the columns the query selects; if adding them to the index lets MySQL satisfy the query from the index alone (Using index), say so and weigh the index-size cost. Remember InnoDB secondary indexes already carry the PK columns. 4. **Detect redundant or duplicate indexes** that should be dropped, and any low-cardinality column that does not belong in the index. 5. **Give the exact DDL** for indexes to add and drop, plus the EXPLAIN signature I should see afterward (key used, Extra showing Using index, no filesort). Output: (a) Recommended index set with CREATE/DROP statements, (b) Per-column justification, (c) Indexes to remove, (d) Verification steps on a replica. Guardrails: build and drop indexes on large tables with gh-ost or pt-online-schema-change, not a blocking ALTER; test on a production-sized replica first and back up beforehand; do not drop an index on prod until you have confirmed via the slow log and performance_schema that nothing depends on it; never run destructive DDL on production without review.
Why this prompt works
Index design is where most MySQL performance is won or lost, and it is deceptively easy to get wrong. The common mistakes are guessable column order, indexes that duplicate an existing leftmost prefix, and missing the chance to make an index covering. This prompt makes the model classify each predicate as equality, range, or sort first, because that classification dictates correct column order — the single most important and most botched decision in composite indexing.
It is grounded in how InnoDB actually stores data. Secondary indexes implicitly include the clustered primary key, which changes the math on covering indexes and on whether you even need to add the PK column. By asking for per-column justification and the expected EXPLAIN signature, the prompt turns “trust me” into a checkable prediction: you should see the chosen key and Using index with no filesort.
The guardrails reflect real operational risk. Adding an index to a multi-million-row table with a plain ALTER can stall writes, so the prompt insists on online schema-change tools and replica testing. It also treats index removal with caution, since a redundant-looking index may be the only thing keeping an untested query fast. The result is a recommendation you can review and roll out safely rather than a wall of CREATE INDEX statements.