What was the initial problem with SQL queries and how did you optimize them?

Initially, SQL query problems are often related to low performance due to:

• Excessive or inefficient JOINs
• Lack of indexes on frequently used columns
• Excessive data retrieval (SELECT *)
• Incorrect use of subqueries

To optimize, we applied the following approaches:

1. Execution plan analysis (EXPLAIN) — identified bottlenecks in queries.
2. Adding indexes — on columns involved in filters and joins.
3. Rewriting queries — replaced subqueries with JOINs, removed SELECT *, and selected only necessary fields.
4. Caching results — if data does not change often.
5. Pagination and limiting — to avoid loading too much data in one query.

Example of optimization:

Before:

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');

After:

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

This approach reduces the number of subqueries and allows the database to better optimize execution.