Indexes

Code

-- Single-column index
CREATE INDEX idx_email ON users(email);

-- Composite index (column order matters)
CREATE INDEX idx_status_created ON orders(status, created_at);

-- Unique index
CREATE UNIQUE INDEX uniq_sku ON products(sku);

-- Fulltext index
CREATE FULLTEXT INDEX ft_title ON articles(title, body);

-- Inspect and drop
SHOW INDEX FROM orders;
DROP INDEX idx_status_created ON orders;

-- Check the execution plan
EXPLAIN SELECT * FROM orders
WHERE status = 'completed' AND created_at >= '2025-01-01';

Explanation

Indexes accelerate lookups but add write overhead and storage; create them based on real queries, not guesses. Composite indexes support leftmost-prefix queries, so order columns by equality then range. EXPLAIN reveals whether MySQL uses an index, scans, or temporary tables.

More MySQL Snippets