How do you optimize a slow-running query?

Optimizing a slow-running query starts with examining the execution plan using EXPLAIN or similar tools. Look for full table scans, missing indexes, or expensive joins. Add appropriate indexes on columns used in WHERE, JOIN, and ORDER BY clauses, ensuring they are selective and not over‑indexed. Consider covering indexes that include all columns needed for the query to avoid lookups. Rewrite the query to reduce complexity: replace subqueries with JOINs, avoid SELECT *, and filter rows as early as possible. Use query hints sparingly to force index usage when the optimizer misbehaves. For large tables, partitioning or sharding can reduce scan scope. Update statistics regularly so the optimizer has accurate cardinality estimates. Finally, test the query after each change, measuring response time and resource usage to confirm improvements. This systematic approach balances index maintenance overhead with performance gains.