SQL Indexes and Query Performance
How database indexes make lookups fast, when they help, and how to design them for predictable query cost.
.jpg)
A database index is a data structure that lets the database find rows without scanning an entire table. Without an index, a lookup by a non-key column requires a full table scan: every row is read and tested, which costs O(n) page reads and grows linearly with the table. With a B-tree index, the same lookup costs O(log n) comparisons and touches only a few pages, because the index is kept sorted and is searched by halving, the same principle used by binary search.
The default index type in MySQL and MariaDB is the B-tree. Each index entry maps an indexed column value to the row's location, and entries are stored in sorted order so that equality checks, range queries, ORDER BY, and prefix searches can all walk the tree efficiently. A primary key is also an index, as is a UNIQUE constraint; both additionally enforce uniqueness.
Indexes are declared with CREATE INDEX or as part of the table definition:
CREATE INDEX idx_articles_category ON articles (category_id);
CREATE UNIQUE INDEX idx_articles_slug ON articles (slug);
CREATE INDEX idx_articles_cat_updated ON articles (category_id, updated_at);
A composite index on multiple columns is most useful when its columns are queried from left to right — the "leftmost prefix" rule. An index on (category_id, updated_at) serves a query filtering by category_id and ordering by updated_at, but it does not help a query that filters only by updated_at. Choosing the column order to match the most important queries is therefore part of index design.
Indexes are not free. Every INSERT, UPDATE, and DELETE must maintain every index on the table, and each index consumes storage. A table with many rarely used indexes can become slower to write than it is fast to read. The practical guidance is to index columns that appear in WHERE, JOIN, and ORDER BY clauses, to prefer selective columns (those with many distinct values), and to verify the plan with EXPLAIN instead of guessing:
EXPLAIN SELECT title FROM articles WHERE category_id = 3 AND status = 'published';
Bounded, indexed queries are the core of keeping a database fast as it grows: the goal is that the cost of a query grows with the index tree depth rather than with the table size. This is the same reasoning that underlies the asymptotic analysis of Big O notation.
Tags
databases indexing performance sql