MySQL Index Types

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);

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

Tags

Related Terms