There is a rite of passage every backend developer goes through: a query that was instant on your laptop grinds to a halt in production once the table has a few million rows. Nine times out of ten, the cure is database indexing — and understanding how indexes work is the difference between an app that scales and one that falls over.
The library analogy that finally makes it click
Imagine a library with no catalog. To find a book by title, you would walk every shelf, checking each spine — a “full table scan.” An index is the catalog: a sorted structure that lets the database jump almost straight to the rows you want instead of reading every one. That is the entire idea. Everything else is detail.
Creating an index
If you frequently look users up by email, index that column:
CREATE INDEX idx_users_email ON users(email);Now a query like SELECT * FROM users WHERE email = 'x@y.com' uses the index to find the row in a handful of steps instead of scanning the whole table. On a large table, that can turn a multi-second query into a sub-millisecond one.
Why not index everything?
Because indexes are not free. Every index you add has to be updated on every insert, update, and delete — so over-indexing quietly taxes all your writes. Indexes also take disk space. The craft is indexing the columns you actually filter, join, and sort on, and no more. An index nobody’s queries use is pure overhead.
Composite indexes and column order
You can index multiple columns together, and the order matters more than people expect:
CREATE INDEX idx_orders_cust_date
ON orders(customer_id, created_at);This index helps queries that filter by customer_id alone, or by customer_id and created_at — but not queries that filter by created_at alone. Think of it like sorting by last name, then first name: the ordering is only useful from the left. Put the most selective, most-filtered column first.
Let the database tell you
Stop guessing and use EXPLAIN (or EXPLAIN ANALYZE). Prefix your slow query with it and the database shows its plan — whether it used an index or fell back to a full scan. That output is the single most useful tool for diagnosing slow queries, and learning to read it will teach you more about database indexing than any article can.
The takeaway
Index the columns you search, join, and sort on; resist the urge to index everything; mind the column order in composite indexes; and let EXPLAIN guide you. Do that, and the “it was fast yesterday” performance cliff mostly stops happening.
