How to find slow queries in a sluggish database and what to do with them?

To find slow queries in a database, you can use built-in profiling and logging tools:

• In MySQL, enable the slow query log, which records queries that run longer than a specified threshold.
• In PostgreSQL, use pg_stat_statements to collect query statistics.
• In Oracle, use AWR reports and SQL Trace.

After identifying slow queries, you should:

1. Analyze execution plans (EXPLAIN) to understand why queries are slow.
2. Check for the presence and efficiency of indexes, and add missing ones if necessary.
3. Optimize the SQL itself: remove redundant JOINs, subqueries, and use aggregations and filters correctly.
4. Consider caching results at the application level.
5. If needed, break complex queries into several simpler ones.

Example of using EXPLAIN in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

This will show how long each stage of the query takes and help identify bottlenecks.