What steps will you take to optimize a long-running SQL query?

To optimize a long-running SQL query, the following steps can be taken:

1. Analyze the execution plan — use EXPLAIN or similar tools to understand which operations take the most time.
2. Add or optimize indexes — ensure that there are appropriate indexes on columns involved in filters and joins.
3. Rewrite the query — simplify complex JOINs, remove redundant subqueries, and use more efficient constructs.
4. Limit the selection — use WHERE and LIMIT to reduce the amount of data processed.
5. Cache results — if data doesn't change often, cache the query results.
6. Optimize database structure — normalize or denormalize tables depending on the situation.

Example of using EXPLAIN in MySQL:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

This helps to understand whether an index on customer_id is used and how many rows are examined.