Indexes

MySQL Indexes

Indexes speed up data retrieval but add overhead to writes.

Creating Indexes

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

-- Composite
CREATE INDEX idx_cat_price ON products(category_id, price);

-- Unique index
CREATE UNIQUE INDEX idx_username ON users(username);

-- Full-text
CREATE FULLTEXT INDEX idx_body ON posts(body);

Viewing Indexes

SHOW INDEX FROM users;

Dropping

DROP INDEX idx_email ON users;

EXPLAIN

EXPLAIN SELECT * FROM products WHERE category_id = 3;
-- Shows if index is being used

Pro Tip

Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses — but don't over-index write-heavy tables.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned