Databases & SQL · reviewed in August 2026
Database index
A database index is an auxiliary structure that speeds up finding rows by the value of one or more columns, similar to a book's index: it lets you locate data without scanning the whole table row by row. It has a cost — it takes up space and slightly slows down every write.
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
Frequently asked questions
When does it make sense to create an index?
On columns frequently filtered on or used for JOINs, over large tables; on small tables or rarely queried columns, an index usually isn't worth its maintenance cost.
Why does an index slow down writes?
Because every INSERT, UPDATE, or DELETE also has to update the index structure, not just the table — more indexes mean more work per write.
Does the primary key already have an index?
Yes, almost every database engine automatically creates an index on the primary key, precisely because it's constantly used for lookups and to relate rows.