Your Dashboard Took Eleven Seconds to Load. An Index Could Fix That.

It's a Tuesday afternoon, and the dashboard that loaded instantly during your first month of launch now takes eleven seconds. Nothing in the code changed. The table just grew from 5,000 rows to 4 million, and a query that once felt free is now the slowest thing in your app. If that sounds familiar, you've met the most common performance problem in databases: a missing or misused index.

This guide explains what an index really is, how to tell when a query needs one, and the handful of mistakes that make an index useless. The examples use MySQL syntax, but the ideas carry over to PostgreSQL, SQLite, and most other relational databases.

What an index actually is

Think of a paperback with no table of contents and no page numbers on the spine. To find the chapter about "refunds," you'd flip through every page. That is a full table scan: the database reads each row, one by one, to check whether it matches your condition.

An index is the book's index at the back. It stores the values of one or more columns in sorted order, each pointing to the row that holds it. Because the values are sorted (usually in a B-tree structure), the database can jump almost directly to the match instead of reading everything. Finding one row among 4 million takes roughly 3–4 steps through the tree rather than 4 million comparisons.

An index doesn't make the database faster. It lets the database skip the work it never needed to do.

The trade-off is real, though. Every index takes disk space, and every INSERT, UPDATE, or DELETE must also update the index. Indexes are not free decoration; they are a bet that you'll read that column far more often than you write it.

Prove it first: read the query plan

Before adding anything, ask the database how it plans to run your query. In MySQL that's EXPLAIN.

EXPLAIN SELECT id, total
FROM orders
WHERE customer_id = 4821
  AND status = 'paid';

Look at three columns in the output. type tells you how rows are accessed: ALL means a full table scan (bad on big tables), while ref or range means an index is helping. key shows which index was chosen, and NULL means none. rows is the estimated number of rows examined; if it's close to the size of the whole table, you're scanning.

A habit worth building: never guess, always EXPLAIN before and after. It turns "I think it's faster" into evidence.

Create your first index

For the query above, the database must find orders for one customer. Add an index on that column:

CREATE INDEX idx_orders_customer ON orders (customer_id);

Run EXPLAIN again. The type should change from ALL to ref, and rows should drop from millions to a handful. On a real table of a few million rows, it is common to see a query drop from seconds to a few milliseconds. The exact gain depends on your data and hardware, but the shape of the improvement is usually dramatic.

Name your indexes consistently (idx_table_column). Six months from now, you'll be grateful when you're scanning a list of them.

Composite indexes and the leftmost rule

Our query filters on two columns: customer_id and status. A composite index covers both:

CREATE INDEX idx_orders_customer_status
  ON orders (customer_id, status);

Here's the part that surprises people: column order matters. A composite index works like a phone book sorted by last name, then first name. You can look up "Smith" quickly, and "Smith, Anna" even faster. But you can't efficiently look up everyone named "Anna" regardless of surname, because the book isn't sorted that way.

This is the leftmost prefix rule. An index on (customer_id, status) helps queries that filter on customer_id alone, or on both columns. It does not help a query that filters only on status.

A practical rule of thumb for ordering columns: put columns you compare with = first, and put range conditions (>, <, BETWEEN) last. If one column is far more selective, meaning it narrows the results much more, lean toward putting it first.

Five ways to accidentally disable an index

You can have a perfect index and still not use it. These are the usual culprits.

1. Wrapping the column in a function. The database can't use a sorted index if it must compute something for every row first.

-- Slow: function applied to the indexed column
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Fast: a range the index can use
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

2. A leading wildcard. LIKE 'smith%' can use an index; LIKE '%smith' cannot, because the sorted order starts from the first character.

3. Type mismatch. Comparing a string column to a number forces implicit conversion on every row. Keep your literal types matching the column type.

4. Skipping the leftmost column of a composite index, as described above.

5. Low-selectivity columns. An index on a column with only two or three distinct values, like a boolean flag, rarely helps because each value matches a huge share of the table. The optimizer will often ignore it and scan anyway.

If EXPLAIN says your index isn't being used, the problem is almost never the index. It's usually how the query was written.

Covering indexes: the quiet speed boost

Sometimes the index contains every column the query needs, so the database never has to touch the table at all. That's a covering index.

CREATE INDEX idx_orders_cover
  ON orders (customer_id, status, total);

SELECT status, total
FROM orders
WHERE customer_id = 4821;

In EXPLAIN, you'll see Using index in the Extra column, which means the answer came straight from the index. This is a strong technique for hot, read-heavy queries, but don't overdo it: wide indexes cost more space and slow down writes.

A simple routine you can follow

Adding indexes at random is how tables end up with fifteen of them and slow writes. Instead, work from evidence:

On a busy production system, create indexes during a quiet window or use your database's online index options, because building an index on a huge table can take time and resources. And always test on a copy of production-sized data; a query that's instant on 1,000 test rows tells you nothing about 4 million.

Wrapping up

An index is a sorted shortcut that lets the database skip work. Read the plan with EXPLAIN, index the columns you filter and join on, mind the column order in composite indexes, and write queries that let the index do its job. Avoid functions on indexed columns, leading wildcards, and indexes on columns with almost no variety.

You don't need to become a database engineer to fix the eleven-second dashboard. You need one EXPLAIN, one well-chosen index, and the patience to measure the result. Your future self, and your users, will thank you.