How do you optimize slow queries that are taking a long time?

First, examine the execution plan with EXPLAIN to identify bottlenecks such as full table scans or expensive joins. Next, add appropriate indexes on columns used in WHERE, JOIN, and ORDER BY clauses; consider composite indexes for multi‑column predicates. Rewrite queries to avoid SELECT * and to use EXISTS instead of IN when appropriate. Partition large tables by date or region to reduce scanned rows. Use covering indexes or materialized views for frequently run aggregations. If the database supports it, enable query caching or use a read replica for heavy reads.

For complex analytical queries, consider pre‑aggregating data into summary tables or using OLAP cubes. Also, tune database configuration parameters like work_mem, shared_buffers, and query_timeout. Finally, monitor performance over time and iterate on indexing and query rewrites. The overall complexity of a well‑indexed query is typically O(log n) for index lookups, whereas a full scan is O(n).