Query Optimisation — Vocabulary and Communication

N+1 problem: fetch 100 blog posts (1 query), then for each post fetch its author (100 queries) = 101 total queries. The fix is eager loading: use a JOIN or preloading to fetch posts and authors in 1–2 queries. ORMs (ActiveRecord, Hibernate) often introduce N+1 silently.

Query hints (SQL Server: OPTION(LOOP JOIN), Oracle: /*+ INDEX(t idx_name) */, MySQL: USE INDEX) override the optimizer's automatic plan selection. Used as a last resort when statistics are misleading and the optimizer consistently chooses a bad plan.

Query parameterization (using bind variables / prepared statements) replaces literal values (WHERE id = 42) with parameters (WHERE id = ?), allowing plan cache reuse and preventing SQL injection. Query normalization is a monitoring concept — stripping literals to group similar queries (used in pg_stat_statements, MySQL Performance Schema).

Connection pooling (PgBouncer, HikariCP, c3p0) keeps a pool of open connections ready for application use. Database connections are expensive to create (TCP handshake, authentication, memory allocation). Pooling dramatically reduces connection overhead for high-throughput applications.

The slow query log (MySQL: slow_query_log, PostgreSQL: log_min_duration_statement) captures queries exceeding a time threshold (e.g., 100ms). It is the starting point for query optimization work — showing which queries run most frequently, which are slowest, and which consume the most total I/O.