What Is a Database Index and How Does It Speed Up Queries?

A database index is a data structure (typically a B-tree) built on one or more columns that lets the database engine locate matching rows quickly, without scanning every row in the table (a 'full table scan').

Common causes

• Without an index, finding rows matching a WHERE condition requires checking every row in the table, which becomes slow as tables grow to millions of rows

How to fix it

• Add an index on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY — CREATE INDEX idx_name ON table(column)
• Use EXPLAIN before a query to see whether it's using an available index or falling back to a full table scan
• Avoid over-indexing — every index speeds up reads but slows down INSERT/UPDATE/DELETE, since the index must be updated too, and consumes additional disk space

Example

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

FAQ

Does adding an index always make queries faster?

Only for queries that can use it — filtering, joining, or sorting on the indexed column(s). It also adds overhead to every write operation on that table, so indexes should be added deliberately, not on every column.