Indexes
SQL · Extra Concepts
Theory
An index is a separate, sorted lookup structure that lets the database find rows without reading the whole table, just like the index at the back of a book. Most databases use a B-tree: a balanced tree of sorted keys where each key points to the matching rows. To find dept = 'IT' among a million rows, the engine walks a few tree levels instead of scanning every row, turning a full scan into a handful of page reads.
Indexes help WHERE filters, JOIN conditions, ORDER BY and MIN/MAX on the indexed columns. A composite index on (dept, salary) also helps queries filtering on dept alone, but usually not on salary alone, because the tree is sorted by the first column first (the leftmost-prefix rule). The cost is that every INSERT, UPDATE and DELETE must also update each index, and indexes take extra storage. Columns with very few distinct values, like a yes/no flag, rarely benefit.
Why we use it
Without an index the database must scan every row to answer a filter, which gets slower as the table grows. An index makes lookups on the indexed columns fast, often reducing seconds to milliseconds.
When to use it
Index columns used often in WHERE, JOIN and ORDER BY on large tables, especially selective ones like ids, emails and dates. Avoid indexing tiny tables, low-variety columns and write-heavy tables where the update cost outweighs faster reads.
How it works
- Query arrives: WHERE dept = 'IT'
- Is there an index on dept? Yes: Walk the B-tree to the 'IT' keys. No: Scan every row of the table.
- Follow pointers to fetch only the matching rows
- Index holds all needed columns? Yes: Answer from the index alone. No: Read the rest from the table.
- Return rows with far fewer reads
Syntax
CREATE INDEX idx_name ON table (column);Example
CREATE INDEX idx_employees_department ON employees (department);Key points
- Best added to columns frequently used in WHERE, JOIN, or ORDER BY
- Every index adds overhead to INSERT/UPDATE/DELETE — don’t over-index
- Composite index (a, b) helps filters on a, or a and b — not b alone
- Primary keys and UNIQUE constraints get an index automatically
- Wrapping the column in a function, e.g. UPPER(name), usually stops index use