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.

Please update your question with the EXPLAIN PLAN for your query — NickW
– NickW, Commented Nov 29, 2023 at 17:49
1) Please copy the result of the explain into the question. 2) Please describe what indexes you have defined on your impacted tables. — Shadow
– Shadow, Commented Nov 29, 2023 at 17:49
please edit your question to add (as text, not images) ouptut of explain select...rest of your query and of show create table yourtablename for all the tables used in the query; this is the minimum information needed to get good answers to optimization questions — ysth
– ysth, Commented Nov 29, 2023 at 18:27
@p3consulting, That's not a problem. Date literals are strings. See dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html — Bill Karwin
– Bill Karwin, Commented Nov 29, 2023 at 18:46
@p3consulting, That syntax is not needed in MySQL. A quoted string that contains a date is automatically cast to a date value when it is compared to a date. Try it! — Bill Karwin
– Bill Karwin, Commented Nov 29, 2023 at 19:11

Rick James · Accepted Answer · 2023-11-29 23:19:52Z

0

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.)

answered Nov 29, 2023 at 23:19

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related