SQL Indexes Explained Simply: What They Are and When to Use Them

Your query worked perfectly when the table had 500 rows. Six months later the table has two million rows, and the same query takes 40 seconds. Nothing in the code changed. What happened?

Most of the time, the answer is a missing index. Indexes are the single biggest performance tool you have in a relational database, and the basic idea is simple enough to learn in one sitting.

Diagram comparing a table scan with an index seek in SQL

The Book Index Analogy

Picture a 600-page book about cooking, and you want every page that mentions “saffron”. You have two choices:

  1. Start at page 1 and read every page until the end.
  2. Flip to the index at the back, find “saffron”, and go straight to pages 112, 340 and 518.

The first option always works, but it’s slow. The second is fast because someone already did the work of sorting the words and noting where they appear.

A database index is exactly that. It’s a separate, sorted structure that stores the values of one or more columns along with pointers to the rows that hold them.

What the Database Does Without an Index

Take this query on a big orders table:

SELECT order_id, amount
FROM orders
WHERE customer_id = 8;

With no index on customer_id, the database has no way of knowing where customer 8’s orders are. It reads every row and checks each one. That’s called a table scan (or a full scan). On a small table nobody notices. On a table with millions of rows, it’s the reason your screen freezes.

Creating an Index

The syntax is short and nearly identical in MySQL, PostgreSQL and SQL Server:

CREATE INDEX ix_orders_customer_id
ON orders (customer_id);

Now the database keeps a sorted list of customer_id values. Most databases store it as a B-tree, which works a bit like a decision tree: “is it below 5,000 or above?”, then “below 7,500 or above?”, and so on. Even with millions of rows, it reaches the right value in just a handful of steps. That’s an index seek.

Table scanIndex seek
How it worksReads every rowFollows the sorted index to the matching rows
Speed on 10 rowsFastFast
Speed on 10 million rowsSlow, gets worse as the table growsStill fast
Needs an index?NoYes

Clustered vs. Non-Clustered Indexes

You’ll see these two terms often, especially in SQL Server:

  • Clustered index: the table’s rows are physically stored in this order. A table can have only one, because rows can only be sorted one way. In SQL Server and MySQL’s InnoDB, the primary key is clustered by default.
  • Non-clustered index: a separate structure that points back to the rows. A table can have many of these.

Think of a phone book. The book itself is sorted by surname (clustered). A separate list at the back sorted by street name, telling you which page to turn to, would be non-clustered.

Which Columns Should You Index?

A good starting list:

  • Primary keys. These are indexed automatically, so there’s nothing to do. (More on keys in Primary Keys vs. Foreign Keys.)
  • Foreign keys. Columns you join on, like orders.customer_id. Some databases don’t index these automatically, and it’s one of the most common causes of slow joins.
  • Columns you filter on often in WHERE clauses, like status, order_date or email. (See WHERE, ORDER BY and LIMIT.)
  • Columns you sort by often with ORDER BY, because an index is already sorted.

Indexes on more than one column

If you regularly filter on two columns together, a composite index can cover both:

CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

Column order matters here. This index helps queries that filter by customer_id, or by customer_id and order_date together. It does very little for a query that only filters by order_date, the same way a phone book sorted by surname then first name doesn’t help you find everyone called “Ahmed”.

The Cost of Indexes

If indexes are so good, why not index every column? Because they aren’t free:

  • Slower writes. Every INSERT, UPDATE and DELETE has to update the indexes as well as the table.
  • More storage. Each index is extra data on disk and in memory.
  • Not always used. On small tables, or when a query returns most of the rows anyway, the database may decide a scan is cheaper and ignore your index.

A table that’s read all day and rarely written can handle quite a few indexes. A table that receives thousands of inserts a minute, like a log table, should have as few as possible.

When an Index Won’t Help

A few patterns stop the database from using an index even when one exists:

PatternWhy it hurtsBetter option
WHERE YEAR(order_date) = 2026Wrapping the column in a function hides it from the indexWHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'
WHERE name LIKE '%ali%'A leading wildcard means it can’t use the sorted orderLIKE 'ali%' if that fits, or full-text search
WHERE phone = 33001111 on a text columnType conversion can force a scanMatch the type: phone = '33001111'

How to Check Whether Your Index Is Used

Every major database can show you its plan for a query:

  • MySQL and PostgreSQL: put EXPLAIN in front of the query.
  • SQL Server: turn on “Include Actual Execution Plan” in SQL Server Management Studio (Ctrl+M) and run the query.

Look for words like “scan” versus “seek” (or “Seq Scan” versus “Index Scan” in PostgreSQL). If you work with Delphi and SQL Server like I do, missing indexes are one of the first things I check when an app starts timing out. I wrote more about that in How to Fix Query Timeout and Performance Issues in Delphi Applications.

In SQL Server, one plan operator is a strong hint that an index is missing: Index Spool (Eager Spool). It means SQL Server is building a temporary index on every run because the real one doesn’t exist. I walk through a real example and the fix in Eager Index Spool in SQL Server: What It Means and How to Fix It.

Indexes in Real Projects

The rules above are the foundation. In a real sales or accounting database, these are the decisions you’ll make again and again.

Column order: equality first, then range

A branch manager opens “this month’s invoices for my branch”. The query filters one column by an exact value and another by a range:

SELECT invoice_date, total
FROM invoices
WHERE branch_id = 7
  AND invoice_date >= '2026-03-01'
  AND invoice_date <  '2026-04-01';

The right index puts the equality column first and the range column second: (branch_id, invoice_date). The database jumps to branch 7, then reads one continuous slice of March. With the columns reversed, it would have to read all of March for every branch and pick out branch 7.

In a test on 300,000 invoices in PostgreSQL, (branch_id, invoice_date) was used for the query above, but a query filtering only on invoice_date ignored it and scanned the whole table, exactly the phone-book effect described earlier. The rule of thumb: columns compared with = first, then the one compared with <, > or BETWEEN, then the one you sort by.

Covering indexes: INCLUDE the columns you read

If the index has the filter columns but not the columns in the SELECT list, the database finds the rows in the index and then has to look up each one in the table. SQL Server calls this a Key Lookup, and with thousands of rows it’s slow. Add the extra columns with INCLUDE:

-- SQL Server, PostgreSQL 11+
CREATE INDEX ix_invoices_branch_date
    ON invoices (branch_id, invoice_date)
    INCLUDE (total);

INCLUDE columns are stored in the index but not sorted, so they’re cheap to add. Keep the list short: a “covering” index with 15 included columns is almost a second copy of the table. MySQL has no INCLUDE; add the column at the end of the key instead. More in Index Seek vs Index Scan and Missing Indexes in SQL Server.

Unique indexes protect your data

An index isn’t only about speed. A unique index on (branch_id, fiscal_year, invoice_no) makes a duplicate invoice number impossible, whatever the application does. A partial (filtered) unique index, such as one on tax_no only WHERE tax_no IS NOT NULL, allows many customers without a tax number but no duplicates among those who have one. See SQL Constraints Explained Simply.

Find indexes nobody uses

Over the years, databases collect indexes that someone added for a report that no longer exists. They still slow down every insert and update. Each database tracks how often each index is used:

-- SQL Server: indexes with no reads since the last restart
SELECT OBJECT_NAME(i.object_id) AS table_name,
       i.name                   AS index_name,
       s.user_updates           AS writes
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
       ON s.object_id   = i.object_id
      AND s.index_id    = i.index_id
      AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.type_desc = 'NONCLUSTERED'
  AND i.is_primary_key = 0
  AND i.is_unique = 0
  AND COALESCE(s.user_seeks + s.user_scans + s.user_lookups, 0) = 0
ORDER BY writes DESC;

-- PostgreSQL
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- MySQL 5.7+ (sys schema)
SELECT * FROM sys.schema_unused_indexes;

These counters reset when the server restarts, so check after a full business cycle, including month-end. An index used only by the year-end closing report looks unused for eleven months. Unique indexes are left out of the SQL Server query on purpose: they protect data even if no query reads them.

Keep statistics fresh

An index is only used well if the database’s statistics about it are up to date. After a big import or a month-end close, outdated statistics can make the optimizer ignore a perfect index. SQL Server Statistics explains how to check and update them.

Frequently Asked Questions

How many indexes is too many?

There’s no fixed number. A reporting table that’s loaded once a night can have ten; a busy order table that takes thousands of inserts an hour should have only the ones you can justify. If you can’t name the query an index is for, it’s a candidate for removal.

Does a primary key create an index automatically?

Yes, in every major database. In SQL Server it becomes the clustered index by default, unless one already exists. A UNIQUE constraint also creates an index. Foreign keys are different: MySQL’s InnoDB indexes them automatically, but SQL Server and PostgreSQL don’t.

What is the difference between a unique index and a unique constraint?

In practice, very little: both create an index and block duplicates. A constraint documents the rule as part of the table’s design; a unique index gives more options, such as a filter or included columns. Use a constraint for business rules and a unique index when you need those extras.

Do indexes help LIKE searches?

Only “starts with” searches: LIKE 'ali%' can use an index on the column, LIKE '%ali%' can’t. For “contains” searches on long text, use full-text search. SQL LIKE, IN and BETWEEN covers both.

Conclusion

An index is a sorted shortcut that lets the database jump to the rows you want instead of reading the whole table. Index your keys and the columns you filter, join and sort on most often, keep an eye on write-heavy tables, and check the execution plan when something feels slow.

Start small. Pick your slowest query, look at its WHERE clause, and see whether the right index exists. Fixing that one query is often enough to make the whole application feel faster.

Related articles

Scroll to Top