Indexes

An index is a separate, sorted data structure that lets MySQL find rows without reading the whole table, just as a book's index lets you jump to a page instead of reading every chapter. Good indexes turn a 10-second query into 1 millisecond. Missing indexes are the number-one cause of slow applications.

How InnoDB indexes work#

InnoDB stores indexes as B+trees, balanced trees whose leaves are kept in sorted order. Finding a value takes a few page reads even among hundreds of millions of rows, and because the leaves are sorted, ranges (BETWEEN, >, LIKE 'abc%') and ORDER BY are cheap too.

• The primary key is the clustered index: the table's rows are physically stored in its leaves, in key order.
• Every other index is a secondary index. Its leaves hold the indexed column values plus the primary key. To fetch other columns, MySQL takes the primary key and looks the row up in the clustered index.

That's why a short primary key (INT/BIGINT) matters: it's copied into every secondary index.

A table worth indexing#

Let's generate 100,000 rows so the difference is visible:

CREATE TABLE users (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(100) NOT NULL,
    last_name  VARCHAR(30)  NOT NULL,
    first_name VARCHAR(30)  NOT NULL,
    city       VARCHAR(20)  NOT NULL,
    created_at DATETIME     NOT NULL
);

SET SESSION cte_max_recursion_depth = 100000;
INSERT INTO users (email, last_name, first_name, city, created_at)
WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 100000)
SELECT CONCAT('user', n, '@example.com'),
       ELT(1 + n % 5, 'Rao', 'Shah', 'Iyer', 'Khan', 'Das'),
       ELT(1 + n % 7, 'Asha', 'Rohan', 'Meera', 'Karan', 'Zoya', 'Imran', 'Neha'),
       ELT(1 + n % 4, 'Pune', 'Delhi', 'Mumbai', 'Chennai'),
       '2024-01-01' + INTERVAL (n % 1000) DAY
FROM seq;
ANALYZE TABLE users;

Seeing the problem with EXPLAIN#

Put EXPLAIN in front of a query to see the plan MySQL would use, without running it. (The next lesson covers EXPLAIN in depth.)

EXPLAIN SELECT id, city FROM users WHERE email = 'user4242@example.com'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 99745
     filtered: 10.00
        Extra: Using where

type: ALL with rows ≈ 100,000 means a full table scan: MySQL reads every row to find one. Now add an index:

CREATE INDEX idx_users_email ON users (email);
EXPLAIN SELECT id, city FROM users WHERE email = 'user4242@example.com'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: idx_users_email
          key: idx_users_email
      key_len: 402
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

type: ref, key: idx_users_email, rows: 1: MySQL jumps straight to the row. Since emails are unique, a UNIQUE index is even better, because it enforces the rule and speeds up lookups.

Creating, listing and dropping indexes#

CREATE INDEX idx_name ON users (last_name, first_name);     -- composite index
CREATE UNIQUE INDEX uq_email ON users (email);              -- unique index
ALTER TABLE users ADD INDEX idx_created (created_at);       -- same thing, via ALTER
DROP INDEX idx_users_email ON users;                        -- redundant now that uq_email exists
SHOW INDEX FROM users;

You can also declare indexes inside CREATE TABLE with INDEX idx_name (col) and UNIQUE (col). PRIMARY KEY, UNIQUE and foreign keys create indexes automatically.

Composite indexes and the leftmost-prefix rule#

An index on (last_name, first_name) is sorted by last_name, then by first_name within each last name, like a phone book. It can serve:

*MySQL 8's skip scan can occasionally help with this, but don't design around it.

Column order matters. Put columns used with = first, then the column used for a range or ORDER BY. For WHERE city = ? AND created_at >= ? ORDER BY created_at, the best index is (city, created_at).

Covering indexes#

If an index contains every column a query needs, MySQL answers the query from the index alone and never touches the table. EXPLAIN shows Using index in the Extra column:

EXPLAIN SELECT first_name FROM users WHERE last_name = 'Rao' AND first_name = 'Asha'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 244
          ref: const,const
         rows: 2857
     filtered: 100.00
        Extra: Using index

The primary key is automatically part of every secondary index, so SELECT id, last_name FROM users WHERE last_name = 'Rao' is covered too. This is another reason to avoid SELECT *: it can never be covered.

Things that stop an index being used#

-- ❌ function on the column
SELECT COUNT(*) FROM users WHERE YEAR(created_at) = 2025;
-- ✅ range on the raw column
SELECT COUNT(*) FROM users WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

+----------+
| COUNT(*) |
+----------+
|    36500 |
+----------+
+----------+
| COUNT(*) |
+----------+
|    36500 |
+----------+

Both give the same answer, but only the second can use idx_created. Other common index-killers:

• Leading wildcards: LIKE '%son'.
• Type mismatches: comparing a VARCHAR column to a number (WHERE phone = 98200) forces a conversion on every row.
• OR across different columns: sometimes MySQL can merge indexes, but often it scans. A UNION of two indexed queries can help.
• Low selectivity: if a condition matches a large share of the table (say city = 'Pune', 25% of rows), a full scan can genuinely be cheaper, and the optimiser knows it.

Functional and prefix indexes#

MySQL 8.0.13+ can index an expression. Note the double parentheses:

CREATE INDEX idx_created_year ON users ((YEAR(created_at)));

For long strings you can index just a prefix: CREATE INDEX idx_email10 ON users (email(10));. Prefix indexes are smaller, but they can't be covering or used for sorting.

The cost of indexes#

Indexes aren't free:

• Every INSERT, UPDATE and DELETE must update every index on the table.
• They take disk space and buffer-pool memory.
• Duplicate or overlapping indexes waste both. (last_name) is redundant if (last_name, first_name) exists.

Guidelines: index primary keys (automatic), foreign keys (automatic in InnoDB), and columns used in frequent WHERE, JOIN, ORDER BY and GROUP BY clauses. Then measure. MySQL's sys schema can show you indexes nobody uses: SELECT * FROM sys.schema_unused_indexes; (needs the Performance Schema enabled).

Invisible indexes#

Not sure an index is still needed? Hide it from the optimiser without dropping it (MySQL 8.0+):

ALTER TABLE users ALTER INDEX idx_created INVISIBLE;
-- watch performance… then either drop it or bring it back:
ALTER TABLE users ALTER INDEX idx_created VISIBLE;

What's next#

You've seen EXPLAIN briefly. Next you'll learn to read it properly, including EXPLAIN ANALYZE, and use it to diagnose and rewrite slow queries.