Skip to content
BENIBIX Learn

Database Index

Development🗃️ DatabasesRelational Databases

Also known as Index, SQL Index, Table Index

A sorted list that lets the database find rows fast. It skips the rest.

Think of it like

Think of it like the index at the back of a book. You jump to the right page instead of reading it all.

Example

Jack's orders table grows to 2 million rows and the search page takes 4 seconds. An index on the email column brings it under 10 milliseconds.

Why it matters

When a big database gets slow, a missing index is most often why.

Definition

A database index is a sorted list that helps the database find rows fast. Without one, it must read every row to find a match, which is called a full scan. With one on the right column, it jumps straight to the rows it needs. Indexes make reads much faster but writes a little slower, because each one must be kept up to date. Primary keys get an index on their own.

Example code

sql
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city = 'London';
-- SCAN customers
CREATE INDEX idx_city ON customers(city);
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city = 'London';
-- SEARCH customers USING INDEX idx_city (city=?)
In SQLite, the scan of every row becomes a search of the index.

Common mistake

Adding an index on every column. Each one slows down writes and takes space, so add them where queries need them.

Key takeaways

  1. 1
    Jump, do not scan. Find rows without reading all.
  2. 2
    Faster reads. But a little slower writes.
  3. 3
    Pick the columns. Index what you search on.