MySQL Index Types
debt(d9/e3/b5/t7)
Closest to 'silent in production until users hit it' (d9). The detection_hints field explicitly states 'automated: no' and the only signal is 'EXPLAIN shows type=ALL on a large table' — meaning the problem is invisible until slow queries manifest under real data volumes in production. No automated tool catches missing indexes proactively; a developer must manually run EXPLAIN on each slow query.
Closest to 'simple parameterised fix (replace pattern with safer alternative)' (e3). The quick_fix states 'Run EXPLAIN on slow queries — if type is ALL, add an index on the filtered column.' Each fix is a targeted ALTER TABLE ADD INDEX statement, slightly above a one-liner (e1) because the developer must first identify the slow query, run EXPLAIN, determine the correct index type and column order, and then apply it — but it remains localized to one schema change per issue.
Closest to 'persistent productivity tax (slows down many work streams)' (b5). Index decisions apply across both web and cli contexts per applies_to. Poor indexing choices (over-indexing or wrong composite order) impose an ongoing tax: INSERT/UPDATE/DELETE performance degrades system-wide, and every new query pattern must be evaluated against existing indexes. It doesn't define the system's shape but it persistently shapes schema evolution and query authoring across the entire team.
Closest to 'serious trap (contradicts how a similar concept works elsewhere)' (t7). The misconception field states 'More indexes are always better' — a widespread belief that is actively harmful. The common_mistakes reinforce multiple non-obvious traps: MySQL does NOT auto-index foreign keys (unlike some other databases), composite index column order matters in a non-obvious way, and FULLTEXT requires its own index type. These behaviors contradict reasonable developer expectations and what other database systems do, making this a serious multi-faceted trap.
Also Known As
TL;DR
Explanation
B-tree (the default) supports equality, range, and prefix queries — suitable for most use cases. FULLTEXT indexes enable natural language search across TEXT/VARCHAR columns using MATCH...AGAINST syntax. HASH indexes (Memory engine only) are O(1) for equality but cannot handle ranges or sorting. Composite indexes work left-to-right — a (first_name, last_name) index supports queries on first_name alone but not last_name alone. EXPLAIN shows which index a query uses.
Diagram
flowchart TD
Q[Query: WHERE user_id=42] --> IDX{Index on user_id?}
IDX -->|Yes| SEEK[Index seek<br/>O log n]
IDX -->|No| SCAN[Full table scan<br/>O n]
SEEK --> FAST[2ms]
SCAN --> SLOW[30s on 10M rows]
Common Misconception
Why It Matters
Common Mistakes
- Indexing every column individually instead of using composite indexes for multi-column WHERE clauses.
- Using FULLTEXT search on a B-tree index — FULLTEXT requires its own index type.
- Forgetting to index foreign key columns — MySQL does not auto-index them, causing full scans on JOINs.
Avoid When
- Do not index every column — each index slows INSERT/UPDATE/DELETE.
- Do not add an index without running EXPLAIN first to confirm it will be used.
- Avoid indexing low-cardinality columns like boolean flags — MySQL may skip the index anyway.
When To Use
- Add an index on any column used in WHERE, JOIN ON, or ORDER BY clauses.
- Use composite indexes for multi-column WHERE clauses — order columns by selectivity (most selective first).
- Use FULLTEXT indexes for natural language search on TEXT/VARCHAR columns.
Code Examples
-- No index on filter column — full table scan
SELECT * FROM orders WHERE user_id = 42; -- scans all rows if user_id not indexed-- Composite index for common query pattern
CREATE INDEX idx_user_status ON orders (user_id, status);
-- Supports: WHERE user_id = ? AND status = ?
-- Supports: WHERE user_id = ?
-- Does NOT support alone: WHERE status = ?
-- FULLTEXT for search
CREATE FULLTEXT INDEX idx_body ON posts (title, body);
SELECT * FROM posts WHERE MATCH(title, body) AGAINST ('php security' IN BOOLEAN MODE);