Database Indexing Best Practices for Faster Queries

Introduction

Indexes speed up lookups by letting the database find rows without scanning entire tables. Used wisely, they reduce query time dramatically; used blindly, they slow down writes and waste memory.

Types of indexes

• B-tree indexes: general purpose; support equality and range (=, >, >=, BETWEEN, ORDER BY), and prefix text matches (e.g., LIKE 'abc%').
• Hash indexes: equality only (=); engine-specific support. Prefer B-tree unless you know your engine benefits from hash for exact matches.
• Composite (multi-column) indexes: one index with multiple columns; powerful when queries filter/order by those columns in order.
• Covering indexes: index contains all columns needed to satisfy a query (Postgres: INCLUDE; MySQL/InnoDB: covered if selected columns are in the index).
• Partial/filtered indexes (engine-specific): index only a subset of rows (e.g., active items), shrinking size and improving cache hit rate.
• Expression/functional indexes (engine-specific): index the result of an expression, e.g., LOWER(email).

When to use which

Practical snippets

PostgreSQL

-- Slow query? Inspect it.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name FROM users WHERE email = 'a@b.com';

-- Basic B-tree
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Composite index: (status, created_at) to filter + order
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
  ON orders (status, created_at DESC);

-- Covering index (Postgres 11+): include non-key columns
CREATE INDEX CONCURRENTLY idx_orders_userid_cover
  ON orders (user_id) INCLUDE (total_amount, status);

-- Partial (filtered) index: only open tickets
CREATE INDEX CONCURRENTLY idx_tickets_open
  ON tickets (updated_at) WHERE status = 'open';

-- Expression index: case-insensitive email search
CREATE INDEX CONCURRENTLY idx_users_lower_email
  ON users ((LOWER(email)));

MySQL (InnoDB)

-- See plan
EXPLAIN FORMAT=TREE
SELECT id, name FROM users WHERE email = 'a@b.com';

-- Basic B-tree (default)
CREATE INDEX idx_users_email ON users (email);

-- Composite index: order of columns matters
CREATE INDEX idx_orders_status_created_at
  ON orders (status, created_at);

-- Covering: include columns in the index definition
-- (MySQL will do an index-only scan if all selected columns are in the index)
CREATE INDEX idx_orders_userid_amount_status
  ON orders (user_id, total_amount, status);

-- Functional index (MySQL 8.0+)
CREATE INDEX idx_users_lower_email ON users ((LOWER(email)));

For text search beyond prefixes (e.g., %term%), consider full-text indexes or dedicated search engines. Regular B-trees cannot use a suffix wildcard efficiently.

Best practices

• Start from queries, not from columns. Add indexes to serve actual slow queries; measure with EXPLAIN/ANALYZE.
• Mind selectivity. Index columns with high cardinality (many distinct values). Low-selectivity columns (e.g., boolean) rarely help alone.
• Composite order matters. Put the most selective predicate first and match your common WHERE/ORDER BY patterns.
• Avoid function wrapping in predicates (WHERE LOWER(email)=�) unless you have an expression index to match it.
• Do not over-index. Every index adds write cost (INSERT/UPDATE/DELETE) and takes RAM/disk. Remove unused ones.
• Cover hot endpoints. For read-heavy paths, design covering indexes to eliminate table lookups.
• Keep stats fresh. Run ANALYZE (Postgres) / ANALYZE TABLE (MySQL) so planners make good choices; vacuum as needed.
• Consider clustering. InnoDB�s primary key is clustered; in Postgres you can CLUSTER or use REINDEX for locality (with trade-offs).
• Deploy safely. Use CREATE INDEX CONCURRENTLY (Postgres) or create online in MySQL to avoid long locks.

Conclusion

Effective indexing balances read speed with write overhead. Profile slow queries, choose the right index type (often B-tree), and validate with EXPLAIN. Keep indexes lean and aligned to real workloads for durable performance gains.