What Is an Index in SQL? Explained for Beginners

An index in SQL is a small, sorted lookup structure that helps a database find rows fast, without checking every single row in a table. In plain words: it's like the index at the back of a book. Instead of reading every page to find a topic, you check the index and jump straight to the right page. If you've already learned SELECT, WHERE, JOIN, GROUP BY, and ORDER BY, indexes are the natural next step — they explain why some queries feel instant and others feel slow.

Why does a query feel slow without an index?

Imagine a table called customers with one million rows. You run a query to find one person by email:

SELECT *
FROM customers
WHERE email = 'amy@example.com';

Line 1 picks every column. Line 2 says which table to look in. Line 3 filters rows down to the one where the email matches. Without an index, the database does not know where 'amy@example.com' lives, so it checks row 1, then row 2, then row 3, all the way to row 1,000,000 if needed. This is called a full table scan, and it gets slower as the table grows.

What an index actually is

An index is a separate, sorted copy of one or more columns, plus a pointer back to the full row. Because the copy is sorted, the database can use a fast search (similar to how you'd find a name in a phone book) instead of checking every row one by one. You don't query the index directly — you still write normal SELECT statements. The database decides, on its own, whether to use an index to answer your query faster.

The book index analogy

A book's index lists topics in alphabetical order with a page number next to each one. To find "turtles," you don't read the whole book — you check the index, see "turtles, page 142," and flip straight there. A database index works the same way: it stores sorted values from a column (like email) next to a pointer to the exact row, so the database can jump straight to the matching data.

How to create an index

Here's how to add an index to the email column in the customers table:

CREATE INDEX idx_customers_email
ON customers (email);

Line 1 says "create an index" and gives it a name, idx_customers_email. Naming it isn't required, but a clear name makes it easier to find later. Line 2 says which table and which column to index. After this runs, the same SELECT ... WHERE email = ... query can find the matching row almost instantly, even in a huge table.

Primary keys are already indexed for you

You don't always need to create an index by hand. When you set a PRIMARY KEY, most databases create an index on it automatically, because primary keys are so often used to look up single rows.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
);

Line 1 creates the customers table. Line 2 makes id the primary key, which uniquely identifies each row — the database automatically indexes this column. Lines 3 and 4 add plain, unindexed columns for name and email. That's why a query like WHERE id = 42 is already fast, but WHERE email = '...' is slow until you add an index yourself.

Seeing the difference with EXPLAIN

Most databases have a command that shows you how a query will actually run. In PostgreSQL and MySQL, it's called EXPLAIN:

EXPLAIN SELECT *
FROM customers
WHERE email = 'amy@example.com';

Line 1 tells the database "don't run this query, just tell me your plan." Lines 2 and 3 are the query you're asking about. Before adding an index, the output will mention a "Seq Scan" (sequential scan), meaning it checks every row. After adding the index from earlier, it should switch to an "Index Scan," meaning it jumps straight to the matching rows.

The trade-off: indexes aren't free

Indexes make reading data (SELECT) faster, but they make writing data (INSERT, UPDATE, DELETE) a little slower. That's because every time a row changes, the database has to update the index too, not just the table. Indexes also use extra disk space. This is why you shouldn't index every column "just in case" — only index columns you actually search or filter on often.

When should you add an index?

• Columns you use often in a WHERE clause, like email or status.
• Columns you use to JOIN tables together, like customer_id in an orders table.
• Columns you sort by often with ORDER BY, especially on large tables.
• Columns with many different values (like email), not columns with only a few (like a yes/no flag).

Common beginner mistakes with indexes

• Indexing every column "to be safe" — this slows down every INSERT and UPDATE for little benefit.
• Forgetting that a table's primary key is usually already indexed automatically.
• Adding an index but never checking with EXPLAIN whether the database actually uses it.
• Indexing a column that only has a few possible values (like true/false), where an index barely helps.

Indexes are one of the simplest ways to make a slow database feel fast, but only when used on the right columns. Start small: add an index to a column you filter or join on often, check the difference with EXPLAIN, and grow from there.