Does adding an index slow down inserts?

Yes, every INSERT and UPDATE must also update the index, so write-heavy tables pay a small tax per index. Keep indexes on columns you actually filter or join on, and avoid piling on indexes you never use.

Why did my index not speed up the query?

The engine likely did not use it. Common causes are wrapping the column in a function, using a leading wildcard in LIKE, or comparing mismatched types. Run EXPLAIN and check whether the key column names your index.

What is a composite index?

A composite index covers two or more columns in a fixed order, such as (Region, OrderDate). It helps queries that filter on the leading column, and it can also serve the second column when the first is constrained.

Should I index every column to be safe?

No. Each index costs disk and write speed, and too many can confuse the optimizer. Index the columns in WHERE, JOIN ON, and ORDER BY that your reports use most, then measure the result.

How do I read EXPLAIN output?

Look for the access type and the rows estimate on the largest table. A sequential scan with a high row count means no index was used; an index scan or seek with a low row count means the index is working.

SQL Performance and Indexing Basics

Add an index on the columns you filter and join on before you touch query structure; that single change fixes most slow reports. In my experience, indexing Orders.CustomerID and Sales.Region cut a 12-second dashboard query to under 1 second, about a 92 percent drop. An index is a sorted lookup structure that lets the engine jump to the rows it needs instead of scanning every row in the table. Use EXPLAIN to see whether your query does a full scan or an index seek, then add the missing index. This guide walks you through reading the plan, picking index columns, and avoiding the mistakes that make indexes useless.

Why a query slows down

A slow query usually means the engine is reading more rows than it needs. On a table with a million rows, a full scan touches all one million even when your report only wants fifty. The fix is almost never to rewrite the SELECT list; it is to give the engine a faster path to the rows that matter. Think of an index like the tabs in a paper ledger: without them you flip every page, with them you open straight to the right section.

Measure before you change anything

Run the query and note the time, for example 8.4 seconds on the Orders table. Capture that baseline so you can prove the index helped instead of guessing. A number you cannot compare is just a feeling.

Count the rows you actually need

Wrap your filter

SELECT COUNT(*)
FROM Orders
WHERE Region = 'West'

If that returns 12,000 of 1,000,000 rows, the engine should seek, not scan. The gap between total and matched rows is your indexing opportunity.

Pro Tip

I recommend timing every report before and after a change and writing both numbers in a comment. A common mistake is shipping an index because it felt faster, then finding it helped a different query and did nothing for the slow one.

How an index actually works

An index is a separate structure that stores the indexed column values in sorted order, each pointing back to the full row. When you filter on that column, the engine searches the small sorted index, finds the matches, and fetches only those rows. The cost is storage and a small slowdown on inserts and updates, because the index must stay in sync. You trade write speed and disk for read speed, which is the right trade for report-heavy tables.

Picture a phone-book lookup

Looking up a name in a sorted book is fast; reading every name to find one is slow. An index on LastName gives you the book order. That mental model explains why filtering on an indexed column is cheap and filtering on an unindexed one is not.

See the index and the table as two objects

The table holds the rows; the index holds the sorted key plus a row pointer. A query that only needs the indexed column can answer from the index alone, which is called a covering index and is the fastest case.

Remember writes pay a tax

Every INSERT or UPDATE on Orders also updates its indexes. A table with ten indexes writes slower than one with two. Avoid indexing columns you never filter or join on, or you pay the tax for nothing.

Reading an EXPLAIN plan

EXPLAIN shows the steps the engine plans to run, including whether it scans the whole table or seeks an index. The exact columns differ by engine, but you are looking for the access type and the rows estimate. A sequential scan on a big table is your red flag; an index scan or seek is what you want to see after adding the index.

Run EXPLAIN on the slow query

Type EXPLAIN

SELECT OrderID, Amount
FROM Orders
WHERE Region = 'West'

Read the plan row for Orders. If it says Seq Scan or ALL, the engine reads every row and your filter is not using an index.

Find the rows estimate

The plan shows rows: 1000000 next to the scan. That means the engine expects to touch the full table. After you index Region, rerun EXPLAIN and the rows estimate should drop to roughly your matched count, around 12,000.

Check the key used

Look for a key column naming your index, such as idx_orders_region. If key is NULL, the index was not picked. This can happen if the filter is on a function, like WHERE YEAR(OrderDate) = 2026, which hides the raw column from the index.

Watch Out

A common mistake is trusting EXPLAIN without running the real query twice. The first run warms the cache and can look slow for reasons unrelated to indexing. Run it a few times and compare the stable time, not the cold one.

Choosing which columns to index

Index the columns that appear in WHERE, JOIN ON, and ORDER BY. Those are the columns the engine searches, sorts, or matches on. Skip columns used only in the SELECT list unless you are building a covering index. Start with the single column that filters out the most rows, confirm the speedup, then consider a composite index.

CREATE INDEX idx_orders_region
ON Orders(Region)

Index the filter column

Now WHERE Region = 'West' seeks instead of scans. On our 1,000,000-row table this alone dropped the report from 8.4 to 1.1 seconds.

CREATE INDEX idx_orders_customerid
ON Orders(CustomerID)

Index the join column

Queries that JOIN Customers c ON o.CustomerID = c.CustomerID now match rows through the index instead of a nested loop over every order.

Index the sort column when reports sort

If users always ORDER BY OrderDate DESC, add an index that ends in OrderDate. The engine can return rows already in order and skip a separate sort step, which matters on large result sets.

Composite indexes and column order

A composite index covers two or more columns in a fixed order, like (Region, OrderDate). The order matters because the index is sorted by the first column, then the second. A query that filters on Region can use the index; a query that filters only on OrderDate usually cannot, because OrderDate is the second key and the first is not constrained.

Put the most selective column first

If Region splits rows 50 ways and OrderDate splits them 365 ways, still lead with the column your queries filter on most often. For WHERE Region = 'West' AND OrderDate >= '2026-01-01', use (Region, OrderDate) so both parts of the index apply.

Avoid indexing a column you never lead with

A query that only filters WHERE OrderDate >= '2026-01-01' will not use (Region, OrderDate) well, because Region is not in the predicate. You would need a separate index starting with OrderDate for that path.

Use the index for sorting too

With (Region, OrderDate), WHERE Region = 'West' ORDER BY OrderDate reuses the index order and avoids a sort. That is why column order should follow your filter then your sort.

When indexes actually hurt

Indexes are not free. They use disk, slow down writes, and can confuse the optimizer into a bad plan if they are stale or redundant. A table that is written to constantly but read rarely should carry only the indexes it truly needs. More indexes is not automatically better.

Drop redundant indexes

If you have both (Region) and (Region, OrderDate), the first is largely redundant for queries that already lead with Region. Keep the composite and drop the single column unless the single one serves a distinct sort path.

Watch write-heavy tables

A staging table loaded with 500,000 rows per hour pays an index maintenance cost on every insert. I recommend indexing it only after load, or keeping it to one or two critical indexes, then building reads on a separate reporting copy.

Rebuild when they go stale

After huge deletes, an index can hold empty pages. Run the engine's rebuild or reorganize command on Sales once a quarter so the plan stays accurate and the seek stays cheap.

Mistakes that make indexes useless

The most painful indexing mistakes are silent. The query runs, returns correct numbers, and is still slow because the engine never touched the index. These are the patterns to learn by heart so you stop wasting the index you added.

Do not wrap the column in a function

WHERE YEAR(OrderDate) = 2026 hides OrderDate from a date index. This will break the seek and force a scan. Rewrite as WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01' so the raw column is compared.

Avoid leading wildcards

WHERE Name LIKE '%son' cannot use an index because the match can start anywhere. A common mistake is filtering on a trailing wildcard only, which works, while a leading one kills the index. Prefer 'son%' when the data allows.

Do not mix types

If CustomerID is a string but you pass a number, the engine may cast every row and skip the index. Keep the filter value the same type as the column, and the seek stays intact.

Worked example: a 12-second report fixed

EXPLAIN plan before and after adding an index on the Orders table, showing scan time dropping

A real dashboard query joined Orders to Customers and filtered on Region and OrderDate, then grouped by month. It ran in 12.3 seconds and timed out the report viewer. Here is the fix, step by step, with the measured numbers.

Capture the slow baseline

EXPLAIN

SELECT o.OrderID, c.Name, o.Amount
FROM Orders o
JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE o.Region = 'West' AND o.OrderDate >= '2026-01-01'
GROUP BY DATE_FORMAT(o.OrderDate, '%Y-%m')

Plan showed Seq Scan on Orders, rows 1000000, time 12.3s.

CREATE INDEX idx_orders_region_date
ON Orders(Region, OrderDate)

Add the composite index

This serves both the Region filter and the OrderDate range, and the join to Customers uses idx_orders_customerid.

Rerun and confirm the drop

Same query after the index: EXPLAIN showed Index Scan using idx_orders_region_date, rows 31000, time 0.9 seconds. That is a 92 percent reduction, from 12.3 to 0.9 seconds, with no query rewrite at all.

Keep watching performance over time

A query that is fast today can drift as the table grows from 1 million to 10 million rows. Set a simple habit: when a report crosses a slow threshold, rerun EXPLAIN and check the rows estimate. The plan tells you whether the index still applies or a new one is needed.

Log slow queries

Enable your engine's slow query log with a threshold like 2 seconds. Review it weekly. The same three queries usually dominate, and indexing those three solves most complaints.

Re-check after big data changes

After a bulk load or a large delete on Sales, rerun EXPLAIN on the top report. Statistics can go stale and the optimizer may pick a scan even though the index is correct.

Track the row-count ratio

When matched rows grow past about 20 percent of the table, an index seek may stop winning and the engine may scan anyway. At that point, filter harder or pre-aggregate the hot range into a smaller table.

Next steps to build the habit

Pick your single slowest report this week and run EXPLAIN on it before changing anything. Add one index on the column it filters or joins on most, then measure again and write both times next to each other. Once that clicks, learn composite indexes and covering indexes so one structure serves the filter, the join, and the sort at once. Pair this with date aggregation so your grouped reports stay cheap as data grows.