sql-optimization

Start by identifying the bottleneck with your database's slow query log or execution plan tools, then rewrite anti-patterns like SELECT *, functions in WHERE clauses, and correlated subqueries. Add indexes on filtered and joined columns to support the rewritten query.

Replace OFFSET-based pagination with cursor-based pagination using a WHERE clause on an indexed column such as created_at or id. This avoids scanning and discarding thousands of rows, keeping response time constant regardless of page depth.

Yes, the techniques are database-agnostic and cover MySQL, PostgreSQL, SQL Server, and Oracle. It includes engine-specific monitoring queries such as pg_stat_statements for PostgreSQL and the MySQL slow log.

Indexes are bypassed when WHERE clauses wrap columns in functions like UPPER() or YEAR(), when composite index column order does not match the query, or when OR conditions prevent index usage. Rewriting predicates to be sargable restores index usage.

Use a covering index when a query repeatedly selects the same columns so the engine can answer entirely from the index. Use a partial index when queries consistently filter on a specific condition, such as active order statuses, to keep the index small.