Database Indexing: Boost Your Query Performance

The orders page loaded fine when the table had a few thousand rows. Now it has a million, the page takes seconds, and the slow query log keeps showing the same innocent-looking SELECT ... WHERE customer_id = ?. Nothing in the code changed. The data grew, and the query never had an index to lean on.

This post covers how MySQL indexes actually work (B-Trees), the index types you'll use, how to read EXPLAIN, composite and covering indexes, what indexes cost you, and the mistakes that quietly stop MySQL from using an index you already have.

What an Index Actually Does

Think of the index at the back of a textbook. You don't read every page to find "replication"; you look it up and jump to page 212. A database index works the same way. It's a separate, sorted data structure that lets the database find rows without reading every row in the table.

Without one, MySQL does a full table scan and checks every row against your WHERE clause. With one, it goes straight to the matching rows.

How Indexes Work: Inside a B-Tree

Most MySQL indexes (every regular InnoDB index) are B-Trees, or more precisely B+Trees. Once you can picture the tree, most indexing rules stop feeling arbitrary.

B-Tree Structure Explained

The tree has a root, some internal levels, and leaf nodes. Each node holds sorted keys and pointers to child nodes; the leaves point at the rows. The tree stays balanced, so every lookup walks the same number of levels no matter which value you ask for.

Why B-Trees Are So Fast

Lookups are logarithmic. In a table with 1 million rows, a B-Tree can find any row in about 20 comparisons (log₂ 1,000,000 ≈ 20). A full table scan may need a million. In practice it's even better than that number suggests: each InnoDB page holds hundreds of keys, so a million-row index is usually only three or four levels deep, which means three or four page reads.

Types of Database Indexes

You'll use the first four constantly. Full-text and spatial indexes are for specific jobs.

Creating Indexes in MySQL

You can declare indexes in CREATE TABLE or add them later. Both are shown below.

Basic Index Creation

-- Create index when creating table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE,           -- Unique index created automatically
    username VARCHAR(100),
    status ENUM('active', 'inactive', 'banned'),
    country VARCHAR(50),
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_status (status),           -- Single column index
    INDEX idx_location (country, city),  -- Composite index
    INDEX idx_created (created_at)       -- Index for date range queries
);

-- Add index to existing table
CREATE INDEX idx_username ON users (username);

-- Add unique index
CREATE UNIQUE INDEX idx_email ON users (email);

-- Add composite index
CREATE INDEX idx_status_country ON users (status, country);

-- Drop an index
DROP INDEX idx_username ON users;

-- Show indexes on a table
SHOW INDEX FROM users;

Analyzing Query Performance with EXPLAIN

EXPLAIN shows how MySQL plans to run a query and which index, if any, it picked. I run it before and after adding any index.

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- EXPLAIN with FORMAT=JSON for detailed info
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE status = 'active' AND country = 'US';

-- EXPLAIN ANALYZE (MySQL 8.0+) - Actually runs the query
EXPLAIN ANALYZE SELECT * FROM users WHERE created_at > '2024-01-01';

Understanding EXPLAIN Output

Composite Indexes: The Leftmost Prefix Rule

A composite index is sorted by its first column, then by the second within each first value, and so on, like a phone book sorted by last name and then first name. That's why the leftmost prefix rule exists: MySQL can only use the index starting from the left, without skipping columns. Get this wrong and you end up with an index that looks right in a migration and never gets picked.

Covering Indexes

If an index contains every column a query needs, MySQL can answer the query from the index alone and never touch the table rows. That's a covering index, and it skips a lookup per matching row, which adds up fast when a query returns thousands of rows.

-- Create a covering index for a common query
CREATE INDEX idx_covering ON orders (customer_id, status, total, created_at);

-- This query is "covered" - no table access needed!
SELECT customer_id, status, total, created_at 
FROM orders 
WHERE customer_id = 123 AND status = 'completed';

-- EXPLAIN will show "Using index" in the Extra column
EXPLAIN SELECT customer_id, status, total, created_at 
FROM orders 
WHERE customer_id = 123 AND status = 'completed';

-- This query is NOT covered (needs 'shipping_address')
SELECT customer_id, status, total, shipping_address 
FROM orders 
WHERE customer_id = 123;
-- MySQL must look up rows in the table for shipping_address

Index Trade-offs: The Cost of Indexes

Indexes aren't free. Each one you add costs you in three places.

Practical Indexing Rules

1. Index Columns in WHERE Clauses

-- If you frequently run this query:
SELECT * FROM orders WHERE customer_id = ? AND status = 'pending';

-- Create an index to support it:
CREATE INDEX idx_customer_status ON orders (customer_id, status);

2. Index Foreign Key Columns

Foreign key columns show up in almost every JOIN, so index them. InnoDB actually creates an index for a foreign key automatically if one doesn't exist, but naming it yourself keeps your schema and migrations readable. Other databases, PostgreSQL included, don't do this for you.

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    price DECIMAL(10,2),
    
    -- Always index foreign keys!
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id),
    
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

3. Avoid Indexing Low-Cardinality Columns

Cardinality is the number of distinct values in a column. If a column has two values split roughly 50/50, an index barely narrows anything down, and MySQL will often ignore it and scan the table anyway. Low-cardinality columns still work well as the second column of a composite index.

4. Use Prefix Indexes for Long Text Columns

MySQL lets you index only the first N characters of a string column. The index gets smaller, but it can't act as a covering index for that column, and you can't use it for ORDER BY on the full value. If you see ERROR 1071 (42000): Specified key was too long on an older setup, a prefix index is the usual fix.

-- For long text columns, index only a prefix
CREATE INDEX idx_title_prefix ON articles (title(50));

-- For URLs, the first 100 chars are usually enough
CREATE INDEX idx_url_prefix ON links (url(100));

-- This reduces index size while maintaining usefulness

5. Monitor and Remove Unused Indexes

-- Check index usage statistics
SELECT 
    object_schema AS database_name,
    object_name AS table_name,
    index_name,
    count_star AS total_accesses,
    count_read AS reads,
    count_write AS writes
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
    AND index_name IS NOT NULL
ORDER BY count_star ASC;

-- Indexes with 0 reads might be candidates for removal
-- But verify first - they might be used by rare but important queries!

Real-World Optimization Example

Here's the full loop on a typical e-commerce orders table: find the slow queries, confirm with EXPLAIN, add indexes, and check again.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
    total DECIMAL(10,2),
    shipping_country VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Common queries that are running slow:
-- 1. Get all orders for a customer
SELECT * FROM orders WHERE customer_id = 12345;

-- 2. Get recent pending orders
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

-- 3. Get orders by country and status for reporting
SELECT shipping_country, COUNT(*), SUM(total) 
FROM orders 
WHERE status = 'delivered' AND created_at > '2024-01-01'
GROUP BY shipping_country;

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

-- Output shows:
-- type: ALL (full table scan!)
-- rows: 1000000 (scanning all rows)
-- Extra: Using where

-- This is BAD - every query scans the entire table!

-- For customer lookups
CREATE INDEX idx_customer ON orders (customer_id);

-- For status + date queries (composite, ordered by selectivity and query pattern)
CREATE INDEX idx_status_created ON orders (status, created_at);

-- For reporting queries
CREATE INDEX idx_status_created_country ON orders (status, created_at, shipping_country);

-- Verify indexes are created
SHOW INDEX FROM orders;

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

-- Output now shows:
-- type: ref (using index!)
-- possible_keys: idx_customer
-- key: idx_customer
-- rows: 47 (only scanning matching rows!)
-- Extra: NULL

-- Query time drops from seconds to milliseconds on a table this size

Common Indexing Mistakes to Avoid

-- BAD: Index on created_at won't be used
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- GOOD: Rewrite to allow index usage
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- BAD: Index won't be used (leading wildcard)
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- GOOD: Trailing wildcard can use index
SELECT * FROM users WHERE email LIKE 'john%';

-- For email domain searches, consider a separate indexed column
-- or use a full-text index

-- If phone_number is VARCHAR:

-- BAD: Numeric comparison causes type conversion
SELECT * FROM users WHERE phone_number = 5551234567;

-- GOOD: String comparison matches column type
SELECT * FROM users WHERE phone_number = '5551234567';

-- This might not use indexes efficiently
SELECT * FROM orders WHERE customer_id = 123 OR status = 'pending';

-- Solution 1: Ensure both columns are indexed
CREATE INDEX idx_customer ON orders (customer_id);
CREATE INDEX idx_status ON orders (status);

-- Solution 2: Use UNION for complex cases
SELECT * FROM orders WHERE customer_id = 123
UNION
SELECT * FROM orders WHERE status = 'pending';

When I'd Add an Index (and When I Wouldn't)

Here's what I go through before adding an index to a production table.