Indicizzazione e ottimizzazione delle query

Without an index, finding a row means scanning every row — a full table scan. As tables grow into millions of rows, this becomes painfully slow.

An index is a separate data structure that lets the database jump straight to matching rows.

Most relational indexes use a B-tree: a balanced tree that keeps keys sorted. Lookups, range scans, and ordering all become logarithmic instead of linear.

• Fast equality lookups (WHERE id = 5)
• Fast range queries (WHERE age > 30)
• Supports ORDER BY without re-sorting

You create an index on the column(s) you frequently filter or sort by.

Here we index the email column so login lookups are instant.

A composite index covers multiple columns. Column order matters: the index helps queries that filter on a left-prefix of the columns.

An index on (country, city) helps WHERE country = ? and WHERE country = ? AND city = ?, but not WHERE city = ? alone.

If an index contains every column a query needs, the database answers from the index alone and never touches the table. This is a covering index.

It is one of the most powerful read optimizations available.

Indexes are not free. Every INSERT, UPDATE, or DELETE must also update each affected index.

• More indexes = slower writes
• More indexes = more storage

Index for the queries you actually run, not speculatively.

Use EXPLAIN (or EXPLAIN ANALYZE) to see the query plan. Look for Index Scan (good) versus Seq Scan (full table scan).

An index helps most when the column is highly selective — it filters down to a tiny fraction of rows. Indexing a boolean is_active with a 50/50 split is nearly useless; the planner may ignore it.

Wrapping an indexed column in a function or doing a leading wildcard defeats the index.

• WHERE LOWER(email) = ? — index on email unused
• WHERE name LIKE '%son' — leading wildcard, no index

Store data in the form you query, or use a functional index.

In a sharded system each shard maintains its own indexes. A query that includes the shard key hits one shard and one index; a query without it must fan out to every shard. Design indexes and shard keys together.

Optimize iteratively: find slow queries from logs, run EXPLAIN, add a targeted (often composite or covering) index, re-measure, and drop indexes that are never used.

You learned how to make reads fast with indexes:

• B-tree indexes power equality, range, and ordering
• Composite indexes follow the left-prefix rule
• Covering indexes answer queries without touching the table
• Indexes cost write speed and storage — index deliberately
• Use EXPLAIN and watch for index-defeating patterns