MySQL Index Tutorial: How to Use EXPLAIN, Composite Indexes and Speed Up Slow Queries

Your PHP or Python app was fast when the database had 50 test rows. Now the client has a year of real data, and the order history page takes several seconds to load. In most cases the fix is not a faster server or a new framework. It is one missing index.

In this MySQL index tutorial you will build a sample database with 500,000 orders, read query plans with EXPLAIN, and add the right indexes step by step: a unique index, a composite index, and a date index. You will also see the common mistakes that make MySQL ignore an index completely. Every query was run on MariaDB 11.8 (a MySQL-compatible server), and all the output below is copied from the terminal.

What Is an Index?

Think of the index at the back of a textbook. To find every page about "recursion", you do not read the whole book; you look up the word in the sorted index and jump to the pages listed. A database index works the same way: it keeps a sorted copy of one or more columns (in a structure called a B-tree) with pointers to the full rows, so MySQL can jump straight to the matching rows instead of reading the entire table.

Without an index, MySQL must do a full table scan: read every row and check the WHERE condition. With 500 rows that is instant. With 500,000 rows, on every page load, for every user, it is not.

Your table already has one index: the PRIMARY KEY. MySQL also creates an index automatically for UNIQUE constraints, and InnoDB creates one for foreign keys if none exists. Everything else is up to you.

Step 1: Create a Sample Database

This script creates an index_demo database with 20,000 customers and 500,000 orders. It uses a small digits table and cross joins to generate rows quickly, so it works on MySQL 8 and MariaDB without stored procedures. Save it as 01_setup.sql:

Run it (it took about two seconds here):

$ mysql -u root -p < 01_setup.sql
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| index_demo.customers | analyze | status   | OK       |
| index_demo.orders    | analyze | status   | OK       |
+----------------------+---------+----------+----------+
+-----------+--------+
| customers | orders |
+-----------+--------+
|     20000 | 500000 |
+-----------+--------+

Note that the tables have no indexes other than the primary keys. That is exactly how many student projects look.

Step 2: Read EXPLAIN and Add a Unique Index

A login page looks up a customer by email. Put EXPLAIN in front of any SELECT to see how MySQL plans to run it, without actually running it:

mysql> EXPLAIN SELECT id, full_name FROM customers WHERE email = '[email protected]'
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | customers | ALL  | NULL          | NULL | NULL    | NULL | 19701 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.000 sec)

mysql> CREATE UNIQUE INDEX uq_customers_email ON customers (email)
Query OK, 0 rows affected (0.015 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT id, full_name FROM customers WHERE email = '[email protected]'
+------+-------------+-----------+-------+--------------------+--------------------+---------+-------+------+-------+
| id   | select_type | table     | type  | possible_keys      | key                | key_len | ref   | rows | Extra |
+------+-------------+-----------+-------+--------------------+--------------------+---------+-------+------+-------+
|    1 | SIMPLE      | customers | const | uq_customers_email | uq_customers_email | 482     | const | 1    |       |
+------+-------------+-----------+-------+--------------------+--------------------+---------+-------+------+-------+
1 row in set (0.000 sec)

The columns to watch in EXPLAIN output:

• type: how rows are found. ALL means a full table scan (bad on big tables). const, eq_ref and ref are index lookups (good). range is an index range scan (usually good). index means reading the whole index.
• possible_keys / key: which indexes MySQL could use, and which one it chose. NULL in key means no index was used.
• rows: MySQL's estimate of how many rows it must examine. Lower is better.
• Extra: notes such as Using where, Using filesort (an extra sort step) and Using index (answered from the index alone).

Before the index, the plan was ALL over about 20,000 rows. After CREATE UNIQUE INDEX, it is const with 1 row. Because emails must be unique anyway, a unique index also protects your data: the database will reject a duplicate email even if your PHP validation has a bug. MySQL 8 shows two extra columns (partitions and filtered), but the meaning is the same.

Step 3: Find the Slow Query

A customer's order history page shows their latest orders:

mysql> SELECT id, created_at, status, total FROM orders WHERE customer_id = 4821 ORDER BY created_at DESC LIMIT 5
+--------+---------------------+---------+---------+
| id     | created_at          | status  | total   |
+--------+---------------------+---------+---------+
| 137636 | 2026-09-13 16:39:40 | paid    | 6703.81 |
| 425208 | 2026-09-13 07:33:00 | pending | 3394.20 |
| 302492 | 2026-09-13 01:46:20 | shipped |  112.42 |
| 282492 | 2026-06-05 20:13:00 | pending | 1999.20 |
| 492780 | 2026-06-05 16:53:00 | paid    | 1972.20 |
+--------+---------------------+---------+---------+
5 rows in set (0.051 sec)

mysql> EXPLAIN SELECT id, created_at, status, total FROM orders WHERE customer_id = 4821 ORDER BY created_at DESC LIMIT 5
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
|    1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 499044 | Using where; Using filesort |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.000 sec)

The query took 0.051 seconds, and EXPLAIN shows why: type ALL over roughly 500,000 rows, plus Using filesort, which means MySQL had to sort the matching rows in an extra step. To return 5 rows, it read the whole table. That might not sound slow, but it grows with the table, and it happens for every visitor at the same time.

Step 4: Add a Composite Index

The query filters by customer_id and sorts by created_at. One composite index on both columns, in that order, handles both jobs:

mysql> CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at)
Query OK, 0 rows affected (0.347 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT id, created_at, status, total FROM orders WHERE customer_id = 4821 ORDER BY created_at DESC LIMIT 5
+--------+---------------------+---------+---------+
| id     | created_at          | status  | total   |
+--------+---------------------+---------+---------+
| 137636 | 2026-09-13 16:39:40 | paid    | 6703.81 |
| 425208 | 2026-09-13 07:33:00 | pending | 3394.20 |
| 302492 | 2026-09-13 01:46:20 | shipped |  112.42 |
| 282492 | 2026-06-05 20:13:00 | pending | 1999.20 |
| 492780 | 2026-06-05 16:53:00 | paid    | 1972.20 |
+--------+---------------------+---------+---------+
5 rows in set (0.001 sec)

mysql> EXPLAIN SELECT id, created_at, status, total FROM orders WHERE customer_id = 4821 ORDER BY created_at DESC LIMIT 5
+------+-------------+--------+------+-----------------------------+-----------------------------+---------+-------+------+-------------+
| id   | select_type | table  | type | possible_keys               | key                         | key_len | ref   | rows | Extra       |
+------+-------------+--------+------+-----------------------------+-----------------------------+---------+-------+------+-------------+
|    1 | SIMPLE      | orders | ref  | idx_orders_customer_created | idx_orders_customer_created | 4       | const | 25   | Using where |
+------+-------------+--------+------+-----------------------------+-----------------------------+---------+-------+------+-------------+
1 row in set (0.000 sec)

The same query now runs in 0.001 seconds. The plan changed to type ref using idx_orders_customer_created, MySQL estimates only 25 rows instead of about 500,000, and Using filesort is gone. Inside the index, all rows of customer 4821 are stored together and already sorted by created_at, so MySQL reads them backward and stops after 5.

A good rule for composite indexes: put the columns you compare with = first, then the column you use for a range or ORDER BY.

Step 5: The Leftmost Prefix Rule

A composite index can be used for its first column alone, or its first and second columns, and so on, but not for the second column alone. It is like a phone book sorted by last name, then first name: easy to find everyone named "Santos", useless for finding everyone named "Maria".

mysql> EXPLAIN SELECT COUNT(*) FROM orders WHERE customer_id = 4821 AND status = 'paid'
+------+-------------+--------+------+-----------------------------+-----------------------------+---------+-------+------+-------------+
| id   | select_type | table  | type | possible_keys               | key                         | key_len | ref   | rows | Extra       |
+------+-------------+--------+------+-----------------------------+-----------------------------+---------+-------+------+-------------+
|    1 | SIMPLE      | orders | ref  | idx_orders_customer_created | idx_orders_customer_created | 4       | const | 25   | Using where |
+------+-------------+--------+------+-----------------------------+-----------------------------+---------+-------+------+-------------+
1 row in set (0.000 sec)

mysql> EXPLAIN SELECT id, total FROM orders WHERE created_at >= '2026-11-01'
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 498971 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.000 sec)

The first query filters on customer_id (the leftmost column) plus status, so the index is used to narrow it down to 25 rows. The second query filters only on created_at, the second column, so the composite index cannot help and MySQL falls back to a full scan. If you need both kinds of queries, you need a separate index that starts with created_at, which we add next.

Step 6: Do Not Wrap Indexed Columns in Functions

A monthly sales report is a classic place to make this mistake. Here is the same report written two ways, after adding an index on created_at:

mysql> CREATE INDEX idx_orders_created ON orders (created_at)
Query OK, 0 rows affected (0.376 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SUM(total) FROM orders WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-10'
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 499044 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.000 sec)

mysql> SELECT SUM(total) FROM orders WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-10'
+--------------+
| SUM(total)   |
+--------------+
| 111753208.41 |
+--------------+
1 row in set (0.083 sec)

mysql> EXPLAIN SELECT SUM(total) FROM orders WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01'
+------+-------------+--------+-------+--------------------+--------------------+---------+------+-------+-----------------------+
| id   | select_type | table  | type  | possible_keys      | key                | key_len | ref  | rows  | Extra                 |
+------+-------------+--------+-------+--------------------+--------------------+---------+------+-------+-----------------------+
|    1 | SIMPLE      | orders | range | idx_orders_created | idx_orders_created | 5       | NULL | 44556 | Using index condition |
+------+-------------+--------+-------+--------------------+--------------------+---------+------+-------+-----------------------+
1 row in set (0.000 sec)

mysql> SELECT SUM(total) FROM orders WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01'
+--------------+
| SUM(total)   |
+--------------+
| 111753208.41 |
+--------------+
1 row in set (0.032 sec)

Both queries return the same total, but the first one ignores the new index (type ALL, possible_keys NULL) and took 0.083 seconds. The reason: the index stores created_at values, not DATE_FORMAT(created_at, '%Y-%m') values, so MySQL has to compute the function for every row. The second version compares the raw column with a range, so MySQL can use the index (type range) and took 0.032 seconds.

The same applies to WHERE LOWER(email) = ..., WHERE price * 1.12 > 1000 and WHERE CAST(...). Keep the indexed column alone on one side of the comparison. For date filters, the pattern col >= start AND col < next_start is the safest choice. (Some newer server versions can rewrite a few simple cases such as YEAR(col) = 2026 automatically, but you should not rely on that.)

Step 7: LIKE and Indexes

mysql> EXPLAIN SELECT id, email FROM customers WHERE email LIKE 'customer1234%'
+------+-------------+-----------+-------+--------------------+--------------------+---------+------+------+--------------------------+
| id   | select_type | table     | type  | possible_keys      | key                | key_len | ref  | rows | Extra                    |
+------+-------------+-----------+-------+--------------------+--------------------+---------+------+------+--------------------------+
|    1 | SIMPLE      | customers | range | uq_customers_email | uq_customers_email | 482     | NULL | 11   | Using where; Using index |
+------+-------------+-----------+-------+--------------------+--------------------+---------+------+------+--------------------------+
1 row in set (0.000 sec)

mysql> EXPLAIN SELECT id, full_name FROM customers WHERE email LIKE '%[email protected]'
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | customers | ALL  | NULL          | NULL | NULL    | NULL | 19701 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.000 sec)

A LIKE pattern that starts with fixed text ('customer1234%') can use the index as a range, just like looking up words that start with "cust" in a dictionary. A pattern that starts with a wildcard ('%[email protected]') cannot, so MySQL scans every row. If your search box needs "contains" searches on large text columns, look into a FULLTEXT index instead. The Using index note on the first query means MySQL answered it from the index alone without touching the table rows, because both id and email are in the index (InnoDB secondary indexes always include the primary key).

Step 8: Not Every Column Needs an Index

What about an index on status? It only has four values, each in about a quarter of the rows:

mysql> CREATE INDEX idx_orders_status ON orders (status)
Query OK, 0 rows affected (0.473 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM orders WHERE status = 'paid'
+------+-------------+--------+------+-------------------+------+---------+------+--------+-------------+
| id   | select_type | table  | type | possible_keys     | key  | key_len | ref  | rows   | Extra       |
+------+-------------+--------+------+-------------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | orders | ALL  | idx_orders_status | NULL | NULL    | NULL | 498898 | Using where |
+------+-------------+--------+------+-------------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)

MySQL lists idx_orders_status under possible_keys but chooses not to use it (key NULL). When a value matches a large share of the table, jumping back and forth between the index and the table costs more than simply reading the table once, and the optimizer knows it. Columns like status, gender or yes/no flags are poor candidates for an index on their own; they are more useful as an extra column in a composite index, after a selective column.

Indexes also have a cost. Every INSERT, UPDATE and DELETE must update each index, and each index uses disk space and memory. Add indexes for the queries you actually run, not for every column "just in case".

Step 9: List and Remove Indexes

mysql> DROP INDEX idx_orders_status ON orders
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM orders
+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table  | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| orders |          0 | PRIMARY                     |            1 | id          | A         |      498825 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| orders |          1 | idx_orders_customer_created |            1 | customer_id | A         |       20784 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| orders |          1 | idx_orders_customer_created |            2 | created_at  | A         |      498825 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| orders |          1 | idx_orders_created          |            1 | created_at  | A         |      498825 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
4 rows in set (0.001 sec)

SHOW INDEX lists every index with its columns in order (Seq_in_index). Cardinality is an estimate of the number of distinct values: high cardinality, like created_at, means the index is selective. To remove an index that is not helping, use DROP INDEX name ON table. You can also manage indexes in phpMyAdmin under a table's Structure tab.

A Simple Checklist for Your Projects

• Index columns used in WHERE, JOIN ... ON and ORDER BY on large tables, starting with the slowest and most frequent queries.
• Use UNIQUE indexes for values that must not repeat: email, username, student number, OR number.
• Index foreign key columns used in joins, such as orders.customer_id.
• For multi-column filters, create one composite index: equality columns first, then the range or sort column.
• Keep indexed columns out of functions and avoid leading % in LIKE.
• Always confirm with EXPLAIN: look for type ALL on big tables and Using filesort on queries that should be quick.

Conclusion

Indexes are the single biggest performance tool in MySQL, and you only need a few rules to use them well. Find slow queries, run EXPLAIN, add a unique or composite index that matches the WHERE and ORDER BY, and write conditions that leave the indexed column untouched. In this tutorial one composite index turned a full scan of half a million rows into a 25-row lookup. Download the SQL files below, run them on your own MySQL or MariaDB server, and then try EXPLAIN on the slowest page of your current project.