Query hygiene

  • Add composite indexes matching filters/order; avoid leading wildcards.
  • Use EXPLAIN to verify index usage; watch for filesort/temp tables.
  • Prefer keyset pagination over OFFSET for large tables.

Config basics

  • Set innodb_buffer_pool_size (50-70% RAM), innodb_log_file_size, innodb_flush_log_at_trx_commit=1 (durable) or 2 (faster).
  • max_connections aligned with app pool size; avoid connection storms.
  • Enable slow query log with sane threshold; sample for tuning.

App considerations

  • Reuse connections (pooling); avoid long transactions.
  • Limit selected columns; cap payload sizes; avoid large unbounded IN lists.
  • For read-heavy workloads, add replicas; route reads carefully.

Checklist

  • Indexes audited; EXPLAIN reviewed.
  • Buffer pool sized; slow log enabled.
  • Pagination and payloads bounded; connections pooled.