Tell about SQL query optimization. Which query needed optimization? How did you realize it needed tuning? What did you tune?

SQL query optimization begins with analyzing their performance — for example, using EXPLAIN PLAN or a query profiler. One of the queries I optimized was too slow when retrieving data from multiple tables with large volumes.

We realized that optimization was needed when the execution time increased to several seconds, negatively impacting user experience.

For optimization, I:

• Added necessary indexes on columns involved in JOIN and WHERE.
• Rewrote the query, removing redundant subqueries and replacing them with JOIN.
• Used aggregate functions and grouping only where necessary.

Example before optimization:

SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'active' AND c.region = 'EU';

After optimization:

-- Added index on orders(status) and customers(region)
SELECT o.id, o.date, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'active' AND c.region = 'EU';

As a result, the execution time was reduced from 5 seconds to 0.5 seconds.