Understanding Database Indexes

What Is a Database Index?

Imagine a phone book. To find "Smith, John," you don't read every page — you flip to the S section because the book is alphabetized. A database index works the same way.

A database is a tool that stores information on a computer — like a giant table full of names, dates, emails, and numbers. An index is a special helper inside that database that keeps a sorted list of what's inside. When you search for something, the index jumps straight to it instead of scanning every single row.

Without an index, finding one record in a table with a million rows means looking at every single row, one by one. With an index, the database finds it almost instantly.

Speed Changes Everything

Think about a website that shows your profile. When you log in, the site searches for your account in the database. Without an index, the server reads through millions of accounts until it finds yours — which could take several seconds. With an index, it finds you in a split second.

For small projects, slow searches barely matter. But as an app grows — more users, more data, more searches — slow lookups add up fast. A search that takes 3 seconds instead of 0.003 seconds sounds small until thousands of people are doing it every minute.

The Lookup Process

Here's what happens when a database looks for something, with and without an index:

Most databases automatically create an index on columns marked as "primary keys" (like an ID number). You can also add indexes manually to any column you search often — like an email address, a username, or a date.

Adding an Index in SQL

Here's how you'd create a table and then add an index to speed up searches on the email column:

-- Create a users table
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT,
  name TEXT
);

-- Add an index on the email column
CREATE INDEX idx_email ON users(email);

Now if your app runs a search like SELECT * FROM users WHERE email = 'alice@example.com', the database uses the index to jump straight to that row — no full table scan needed. On a table with 100,000 rows, this can be the difference between a query that takes 2 seconds and one that takes 2 milliseconds.

Knowledge Check

Test what you learned with this quick quiz.