What did you pay attention to when optimizing SQL queries? How did you optimize?

When optimizing SQL queries, I pay attention to several key aspects:

• Use of indexes: I check that fields involved in WHERE, JOIN, and ORDER BY conditions are indexed.
• Execution plan analysis (EXPLAIN): I study how the DBMS executes the query to identify full table scans or inefficient operations.
• Minimizing data retrieval: I select only necessary columns, avoiding SELECT *.
• JOIN optimization: I review the order and conditions of joins to reduce the number of processed rows.
• Partitioning usage: If the table is large, I limit queries to the required partitions.
• Result caching: For recurring queries, I use caching at the application or database level.

Example of optimization:

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

-- Optimized:
SELECT o.id, o.date, c.name FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'active';

-- Added indexes on customers.status and orders.customer_id

It is also important to profile queries in real conditions and test changes.