Perché gli indici sono importanti

A database index works like the index at the back of a book — it lets queries jump straight to the data instead of reading everything.

Without an index, finding a row in a big table means a full table scan — checking every record page by page. Painfully slow at scale.

A full table scan reads every single row to find your data. It burns I/O and tanks query performance once tables grow large.

Querying a million-row users table by email with no index? Postgres checks every row until it finds a match. The code shows the slow case.

A database index is a sorted lookup structure mapping column values to row locations — so the engine jumps to the rows it needs and skips the rest.

An index keeps a sorted copy of a column plus pointers to the real rows, so a query lands on matching rows fast instead of scanning the table.

The main payoff is faster SELECT queries — especially with WHERE, ORDER BY, and JOIN — giving snappier apps and lighter server load.

This lookup by product_name flies if that column is indexed, instead of crawling the whole table. Run it and see.

Indexes do more than speed: a UNIQUE constraint is backed by an index, blocking duplicate values and protecting data integrity.

Indexes aren't free. They cost disk space, and every INSERT, UPDATE, or DELETE must update them too — so it's a balance, not a free win.

Based on what you've learned, what is the MOST significant benefit of using indexes in a database?

That's the why: indexes skip full table scans to speed up SELECTs and enforce uniqueness — at a small storage and write cost. Next: B-trees.