DatabaseAlso called: "database optimization", "query tuning"
Query optimization makes databases faster and more scalable.
Optimization techniques
- Indexing: Create indexes on frequently queried columns.
- Query rewriting: Simplify complex queries.
- Join optimization: Choose appropriate join types.
- Avoid SELECT *: Request only needed columns.
- Limit result sets: Use LIMIT/TOP/FETCH.
- Batch operations: Group INSERT/UPDATE statements.
Index strategies
- Primary key indexes (automatic).
- Covering indexes (include all query columns).
- Composite indexes (multiple columns).
- Avoid over-indexing (slows writes).
Query analysis
- EXPLAIN/EXPLAIN ANALYZE (show execution plan).
- Identify table scans (bad) vs index seeks (good).
- Check for N+1 query problems.
- Monitor slow query logs.
Common anti-patterns
- Implicit type conversions.
- Functions on indexed columns (breaks index).
- OR conditions (consider UNION).
- Subqueries (consider JOINs).
Caching strategies
- Result caching (Redis, Memcached).
- Query caching (MySQL query cache).
- Application-level caching.