Introduction to Database Indexing: Why It Matters
A database index is a data structure that speeds up data retrieval at the cost of additional storage and slower writes. Without indexes, MySQL scans every row to find matching data. With the right indexes, MySQL can find the exact rows it needs in milliseconds. Most performance problems in MySQL are caused by missing indexes.
Most slow queries share the same root cause: a missing index. Not because MySQL was slow, not because the server was underpowered, not because the query was poorly written. Just because a column did not have an index.
What Is an Index?
Think of an index like a book's index. If you want to find every mention of "MySQL" in a 500-page book, you could read every page (full table scan). Or you could check the index at the back and go directly to pages 45, 122, 203, and 387 (index lookup).
A database index works the same way. It is a separate data structure that stores the indexed column values and pointers to the rows containing those values. When you query an indexed column, MySQL looks up the value in the index and follows the pointer directly to the matching rows.
-- Without index: Full table scan
SELECT * FROM users WHERE email = 'alice@example.com';
-- MySQL reads every row in the table (1,000,000 rows)
-- Add index
CREATE INDEX idx_users_email ON users(email);
-- With index: Index lookup
SELECT * FROM users WHERE email = 'alice@example.com';
-- MySQL reads the index, finds the pointer, reads 1 rowHow B-Tree Indexes Work
MySQL's InnoDB storage engine uses B-tree indexes. A B-tree is a balanced tree structure that keeps data sorted and allows searches, insertions, and deletions in logarithmic time.
Here is how a B-tree index on the email column might look:
[alice@...]
/ \
[bob@...] [eve@...]
/ \ / \
[alice@...] [carol@...] [dave@...] [frank@...]To find 'carol@example.com', MySQL starts at the root, goes left (bob@...), then goes right (carol@...). That is 3 steps to find any row, regardless of whether the table has 1,000 rows or 1,000,000 rows.
Without the index, MySQL would have to scan every row. With 1,000,000 rows, that is 1,000,000 steps. With the B-tree index, it is 3 steps. That is the difference between 1 second and 0.001 seconds.
When to Add Indexes
Add indexes for columns that appear in:
- WHERE clauses - The most common reason to add an index
- JOIN ON clauses - Foreign keys should almost always be indexed
- ORDER BY clauses - Indexes can avoid expensive sorts
- GROUP BY clauses - Indexes can avoid temporary tables
-- Query filters on user_id and status
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- Add composite index (order matters: most selective first)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);When NOT to Add Indexes
Not every column needs an index. Adding unnecessary indexes:
- Slows down INSERT, UPDATE, and DELETE operations
- Uses disk space
- Can confuse the query optimizer
Do not index:
- Small tables - If the table has fewer than 10,000 rows, a full scan is fast enough
- Low-selectivity columns - Columns with few distinct values (like a boolean
is_active) are not good index candidates - Columns you never query on - If you never filter by it, do not index it
Composite Indexes: Column Order Matters
A composite index covers multiple columns. The order of columns in the index matters because MySQL can only use the index efficiently from left to right.
-- Composite index
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- This query uses the index (user_id is first)
SELECT * FROM orders WHERE user_id = 123;
-- This query uses the index (user_id + status)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- This query CANNOT use this index efficiently (status without user_id)
SELECT * FROM orders WHERE status = 'pending';Think of a phone book. It is sorted by last name, then first name. You can quickly find "Smith, John" because Smith comes before Smithson. But you cannot quickly find all people named "John" because first names are not sorted independently.
The same principle applies to your queries. A composite index on (user_id, status) can serve queries that filter on user_id alone, or on user_id and status together, but not queries that filter on status alone. Understanding this left-to-right rule is the single most important concept when designing composite indexes. Getting it wrong means the index goes unused and your query still does a full scan.
Index Selectivity
Selectivity is the ratio of distinct values to total rows. High selectivity means the index is effective at narrowing down results.
-- High selectivity (good for indexing)
-- email: 1,000,000 distinct values in 1,000,000 rows = 100% selectivity
SELECT COUNT(DISTINCT email) / COUNT(*) FROM users;
-- Low selectivity (bad for indexing)
-- is_active: 2 distinct values in 1,000,000 rows = 0.0002% selectivity
SELECT COUNT(DISTINCT is_active) / COUNT(*) FROM users;High-selectivity columns (email, username, UUID) are excellent index candidates. Low-selectivity columns (boolean flags, status with few values) are poor index candidates.
Prefix Indexes for Long Strings
If you need to index a long VARCHAR column (like a URL or email), you can index only the first N characters. This saves space and is often fast enough.
-- Index only the first 10 characters of email
CREATE INDEX idx_users_email_prefix ON users(email(10));
-- This works for LIKE 'prefix%' patterns
SELECT * FROM users WHERE email LIKE 'alice%';
-- Exact match on a prefix index is not efficient
-- WHERE email = 'alice@example.com' cannot use the prefix index
-- Use a full index for exact-match queriesThe tradeoff: prefix indexes cannot be used for ORDER BY or covering index scans. They are a compromise between space and functionality.
Checking Index Usage with EXPLAIN
Always verify that your indexes are actually being used. EXPLAIN shows you which index MySQL chose, or if it did a full table scan. Run EXPLAIN on every slow query before adding an index, and run it again after to confirm the fix. The output can be confusing at first, but the key columns to focus on are type, key, and rows. Once you learn to read these three fields, you will catch most index problems before they reach production.
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- Look for:
-- type: ref (good) vs ALL (bad)
-- key: idx_orders_user_status (good) vs NULL (bad)
-- rows: 3 (good) vs 1000000 (bad)Key Takeaways
- Most slow queries are caused by missing indexes, not by MySQL being slow
- Composite indexes work left-to-right; the most selective column should come first
- Use EXPLAIN to understand how MySQL executes your queries
- Primary keys automatically create clustered indexes in InnoDB
- Not every column needs an index; too many indexes slow down writes
Real-World Indexing Strategy
A social media platform has a posts table with 50 million rows. Users frequently search by author, filter by creation date, and sort by likes. Here is how to index this table for those query patterns:
-- Posts table with 50 million rows
CREATE TABLE posts (
id BIGINT PRIMARY KEY,
author_id INT NOT NULL,
content TEXT,
created_at DATETIME NOT NULL,
likes INT DEFAULT 0,
status ENUM('draft', 'published', 'archived')
);
-- Index for "show me all posts by a specific user"
CREATE INDEX idx_posts_author ON posts(author_id);
-- Index for "show recent published posts" (used in WHERE and ORDER BY)
CREATE INDEX idx_posts_status_created ON posts(status, created_at);
-- Index for "trending posts sorted by likes"
CREATE INDEX idx_posts_likes ON posts(likes DESC);The composite index on (status, created_at) is the most important. It serves the homepage feed query which filters by published status and sorts by date. Without this index, MySQL would scan millions of rows and use a filesort. With the index, it reads a small range from the index in order.
Performance Impact of Proper Indexing
The difference between an unindexed and indexed query on a large table is dramatic. Here is what each query type looks like in practice:
- Full table scan on 50M rows - 10-30 seconds. MySQL reads every row, evaluates the WHERE clause, and returns matches.
- Index lookup on 50M rows - 0.001-0.01 seconds. MySQL traverses 3-4 B-tree levels and reads the matching rows directly.
- Covering index scan - 0.001-0.005 seconds. MySQL reads only the index, which is smaller than the table data.
The rule of thumb: if a query takes more than 100ms on your expected data volume, check if an index can help. Use EXPLAIN to see if MySQL is doing a full table scan (type: ALL) or an index lookup (type: ref, range, or eq_ref).
FAQ
How many indexes should I have per table?
There is no fixed number. Index the columns you query on. A table might have 1 index or 10. Each index slows down writes, so add them incrementally based on actual query patterns, not hypothetical ones.
Do primary keys automatically get indexed?
Yes. InnoDB automatically creates a clustered index on the primary key. You do not need to add an index for the primary key column.
What is the difference between a primary key and a unique index?
Both enforce uniqueness. A primary key cannot be NULL and there can only be one per table. A unique index allows multiple NULL values (since NULL ≠ NULL per SQL standard) and you can have multiple unique indexes per table.
Can indexes slow down reads?
Rarely. If the optimizer chooses the wrong index, it can be slower than a full scan. This usually happens with low-selectivity indexes or outdated statistics. Run ANALYZE TABLE to update statistics if you suspect this.
How do I find slow queries that need indexes?
Enable the slow query log by setting slow_query_log = 1 and long_query_time = 1 in my.cnf. Queries taking longer than 1 second are logged. Review the log daily and add indexes for the most frequent slow queries.
Written by
MasterSQL