What Is an Index in SQL?

An index is a second, sorted copy of some of a table's data, keyed on the values you index. Its whole purpose is to let the engine answer a predicate by seeking straight to the matching rows instead of reading every row and testing each one.

Without an index, a filter on customer_id means the engine reads the table from the beginning, applies the predicate to every row, and keeps the ones that match. That cost is proportional to the size of the table. With a sorted index on customer_id, the engine can binary search to the right position and read only the matching entries — a cost proportional to the number of matches plus a logarithmic search. On a table with a million rows where forty match, that is the difference between reading a million rows and reading forty.

The cost is on the write side. Every index has to be kept in step with the table, so every INSERT has to add an entry to every index, and every UPDATE that changes an indexed column has to update it. Every DELETE has to remove an entry from each one. An index also takes disk space, and a large index is slower to keep cached in memory than a small table, which can push other work out of the buffer pool.

There is also a subtler cost: the planner has more choices. With ten indexes on a table, the optimizer has to evaluate ten possible access paths against the table's statistics, and a bad estimate can lead it to a worse plan than it would have found otherwise. Indexes are not free in planning time either.

The practical framing is that an index is a bet: you pay a little on every write in exchange for making a particular read dramatically cheaper. That bet pays off when the read is frequent, the read touches a large fraction of the table, and the index is selective. It loses when the indexed column changes constantly or when almost every query reads most of the rows anyway.

EXPLAIN QUERY PLAN shows the difference directly: a scan before the index, a search after it.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (customer_id, amount) VALUES
  (1, 120.00), (1, 340.50), (2, 75.25), (2, 610.00), (3,  40.00);

-- Before the index: every row is read and tested.
EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE customer_id = 1;

CREATE INDEX idx_orders_customer ON orders (customer_id);

-- After the index: the engine seeks straight to the matching rows.
EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE customer_id = 1;

SELECT id, amount FROM orders WHERE customer_id = 1 ORDER BY id;

The first plan says SCAN orders. The second says SEARCH orders USING INDEX idx_orders_customer (customer_id=?). The final query returns the two matching rows, order ids 1 and 2.

Two words to know in these plans. SCAN means the engine reads every row of the table and evaluates the predicate on each one. SEARCH means it used an index to find a subset — a seek rather than a scan. The presence of USING INDEX, followed by the index name and the column, tells you exactly which index did the work and how it was used.

The important caveat is that a plan is not a measurement of time. The plan says what the engine intends to do; whether that is fast depends on how many rows match and how large the table is. On a table with five rows, the scan might genuinely be faster than the index seek, because seeking has overhead too. The index pays off at scale, which is why you should look at the plan on a realistic dataset rather than on a toy one.

Also worth checking when a plan looks surprising: whether the index is actually selective. If a query matches most of the table, the planner may correctly decide that a scan is cheaper than a seek, and it will ignore your index even though it exists. That is not a failure of the index — it is the optimizer telling you that the predicate was not selective enough for the index to be worth using, and that a different index or a different query is what you need.