How have you optimized SQL queries?

SQL query optimization includes several approaches:

• Using indexes: creating indexes on columns involved in WHERE, JOIN, and ORDER BY clauses to speed up searches.
• **Avoiding SELECT ***: selecting only necessary columns reduces the amount of data transmitted.
• Optimizing JOINs: choosing the right type of JOIN and the order of tables, as well as using subqueries or CTEs when needed.
• Analyzing execution plans (EXPLAIN): helps understand how the DBMS executes the query and identify bottlenecks.
• Caching results: if data doesn't change often, query results can be cached.
• Pagination: for large datasets, use LIMIT and OFFSET.

Example of query optimization:

-- Non-optimized query
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.country = 'USA';

-- Optimized query
SELECT o.id, o.date, c.name FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'USA';

Here, we select only the necessary fields, reducing network and memory load.