Database Performance: Indexing and Query Optimisation
Database performance problems are one of the most common causes of slow application response times. Understanding indexing and query optimisation helps you diagnose and resolve them — or build systems that avoid them from the outset.
Indexes Explained
A database index is a data structure that enables fast lookup of rows by specific column values — analogous to an index in a book. Without an index, the database performs a full table scan (reading every row) to find matching records. With an index, it jumps directly to the relevant rows.
Types of Indexes
- B-tree index: The default index type — supports equality and range queries. Used for most columns queried with WHERE, JOIN, ORDER BY.
- Composite index: An index on multiple columns — useful when queries filter on multiple columns simultaneously. Column order matters.
- Partial index: An index on a subset of rows — reduces index size and maintenance cost
- Full-text index: Enables text search queries — word stemming, relevance ranking
- Unique index: Enforces uniqueness while also enabling fast lookup
Query Optimisation Techniques
- EXPLAIN / EXPLAIN ANALYZE: Shows how the database executes a query — reveals full table scans, inefficient joins, missing indexes
- Avoid SELECT *: Only select the columns you need — reduces I/O
- Avoid N+1 queries: Use JOINs or eager loading rather than querying in a loop
- Pagination: Use LIMIT and OFFSET, or cursor-based pagination for large datasets
- Connection pooling: Reuse database connections rather than creating a new connection per request