How did you optimize queries from 10-20 minutes to 5-7? What reports took so long?

Optimizing long queries (10-20 minutes down to 5-7 minutes) usually involves several steps:

1. Execution plan analysis — using EXPLAIN or similar tools to identify bottlenecks.
2. Indexing — adding or adjusting indexes on frequently used filter and join fields.
3. Query rewriting — simplifying complex JOINs, replacing subqueries with more efficient constructs.
4. Caching intermediate results — if reports are built on large data volumes, storing aggregates in separate tables.
5. Parallelization and batching — splitting queries into parts or running them in parallel.

What kind of reports? Usually, these are complex analytical reports with many aggregations and filters on historical data, such as sales reports, user behavior reports, or financial summaries, where data is gathered from multiple tables with millions of records.

An example of optimization in Go using prepared statements and indexes:

rows, err := db.Query("SELECT user_id, COUNT(*) FROM orders WHERE order_date > ? GROUP BY user_id", startDate)
// Ensure there is an index on order_date

The main thing is to understand where exactly the query is slow and eliminate that cause.