Database Indexes Explained (Speed Up Queries)

When your table grows, queries that were fast can become slow.

An index is like a book’s table of contents:

• without index: MySQL scans many rows
• with index: MySQL jumps directly to matching rows

Simple example

If you often run:

• SELECT * FROM users WHERE email = ?

Then you want an index on email.

CREATE INDEX idx_users_email ON users(email);

Important tradeoff

Indexes:
✅ speed up reads
❌ slow down writes (INSERT/UPDATE) a bit, because the index must be updated

When to add an index

• columns used in WHERE
• columns used in JOIN
• columns used in ORDER BY (sometimes)

Visual idea

flowchart LR
  A[Query] --> B{Index exists?}
  B -->|Yes| C[Fast lookup]
  B -->|No| D[Full table scan]

Next: Pagination, show results page-by-page without loading everything.