Tell me about SQL query optimization by 95% — what was the problem?

SQL query optimization was achieved by 95% through several key changes:

• Initially, queries used multiple nested subqueries and JOINs without indexes, leading to full table scans.
• I analyzed execution plans and added necessary indexes on frequently used columns.
• Rewrote queries, replacing subqueries with JOINs where possible, and removed redundant selections.
• Used aggregate functions and groupings on the database side to reduce the amount of data transferred.

Example:

-- Was
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'EU');

-- Became
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.region = 'EU';

As a result, query execution time was reduced from several seconds to fractions of a second.