Database Indexes
In depthConcept
Also known as: Indexen
Data structures that speed up database lookups, like the index at the back of a book.
Database Indexes, explained
Written by EduVerseWhat it is
A database index is an extra structure, often a B-tree, that keeps the values of one or more columns sorted with pointers to the matching rows. With it, the database can jump straight to what you ask for. Without it, it has to read the whole table row by row.
Why teams use it
On a small table a full scan is quick, but on millions of rows a query without a usable index can take seconds instead of milliseconds. Indexes aren’t free, though: they take disk space and slow down inserts and updates a little, so you add them where queries actually need them.
An example from work
A search on email in the admin panel suddenly takes four seconds now that the users table has grown. You run EXPLAIN ANALYZE in PostgreSQL and see a Seq Scan on users. After a CREATE INDEX on the email column, the plan shows an Index Scan and the query returns in a few milliseconds.
Our own explanation, not a quote from the book.
Where it fits
Query optimisation in databases
Coverage in the book
Covered in depth: multiple pages with explanations, examples and simulations.
