What is it?
An index is a separate data structure that lets MySQL jump directly to matching rows instead of scanning every row. Like a book index, it trades storage space for dramatically faster lookups.
Why does it matter?
A query that takes 5 seconds on a million-row table can run in 5 milliseconds with the right index. Indexes are the most impactful performance optimisation available, and EXPLAIN is the tool to find where they are needed.
Learn indexes, EXPLAIN, when to add them, and why over-indexing slows writes.
Real-World Use Cases
- 🔍 User login lookup - Index on email column -- SELECT WHERE email=? goes from full scan (500ms) to instant (0.1ms) on 1M users.
- 📦 Order history by user - Index on orders.user_id -- fetching a user's orders goes from scanning all orders to a direct index lookup.
- 📅 Date-range reports - Index on created_at -- WHERE created_at BETWEEN date1 AND date2 uses the index as a fast range scan.
- 🔗 JOIN optimisation - Foreign key columns used in JOINs should always be indexed -- drastically speeds up multi-table queries.
Creating Indexes
-- Single-column index
CREATE INDEX idx_email ON users (email);
-- Unique index -- speeds up lookups + enforces no duplicates
CREATE UNIQUE INDEX idx_email_unique ON users (email);
-- Composite index -- for queries filtering BOTH columns
CREATE INDEX idx_city_age ON users (city, age);
DROP INDEX idx_email ON users;Using EXPLAIN to Diagnose Slow Queries
EXPLAIN SELECT * FROM users WHERE email = 'rahul@example.com';
-- type: ALL = full table scan (BAD!)
-- ref / eq_ref = index lookup (GOOD!)
-- key: which index was used (NULL = no index)
-- rows: estimated rows MySQL must examine (lower = faster)
-- Extra: 'Using filesort' or 'Using temporary' = red flagsWhen to Add -- and Avoid -- Indexes
-- GOOD: columns used in WHERE, JOIN, ORDER BY
CREATE INDEX idx_status ON orders (status);
CREATE INDEX idx_user_id ON orders (user_id);
CREATE INDEX idx_created_at ON posts (created_at);
-- AVOID over-indexing:
-- Every index slows down INSERT / UPDATE / DELETE
-- Only index columns you actually filter, join, or sort onQ: Does MySQL automatically create indexes?
MySQL auto-creates indexes for PRIMARY KEY and UNIQUE columns. InnoDB indexes foreign key columns. All other indexes must be created manually. Use EXPLAIN to find full-scan queries and add targeted indexes.
Comments (0)
No comments yet. Be the first!
Leave a Comment