How can I optimize the performance of a slow-running query in MySQL?

Question

I have a query that is taking over 30 seconds to run and is causing delays in our application. I've tried indexing the tables and adjusting the query structure, but it's still slow. Are there any other optimization techniques I can use to speed up the query?

Thank you.

SELECT
    orders.order_id,
    orders.order_date,
    customers.customer_name,
    products.product_name,
    order_details.quantity,
    order_details.unit_price
FROM
    orders
JOIN
    customers ON orders.customer_id = customers.customer_id
JOIN
    order_details ON orders.order_id = order_details.order_id
JOIN
    products ON order_details.product_id = products.product_id
WHERE
    orders.order_date >= '2022-01-01'
    AND products.category = 'Electronics'
ORDER BY
    orders.order_date DESC;

ERROR: Query timeout expired. The query took more than 30 seconds to execute.

Answer 1

These composite indexes may help:

orders:  INDEX(order_date, customer_id,  order_id)
customers:  INDEX(customer_id,  customer_name)
products:  INDEX(category, product_id,  product_name)

If not, please provide `SHOW CREATE TABLE for each table so we can di deeper.

(And, I agree that orders.order_date >= '2022-01-01' is fine.)