MySQL Left join makes query very slow...how can I refactor?

Question

I have PHP system that runs a MYSQL query like below

select
order.id,
order.name,
order.date,
customer.name,
items.coupon_code,
from order
left join customer on order.custid = customer.id
left join items on items.coupon_code = order.coupon_code
where items.coupon_new_code is null
and order.status = 1000
AND order.promo_code in (1,2)

order table has 800K records and items table has 300k records. When I run this the query takes about 9 hours to finish!

If I comment the left join to the items table then the query runs in a few seconds! I am not very efficient with MySQL joins and would really really appreciate if someone can tell me how I can optimise this query to run in an acceptable time frame.

What is the desired result? You want only orders that have customers and items? Is it possible to have an order without a customer or an order without items? — Dan, May 07 '18 at 00:21
Can you include the model of the tables involved? Or at least from the fields used in the joins and where clause? (SHOW CREATE TABLE order; SHOW CREATE TABLE customer; SHOW CREATE TABLE items;) — pah, May 07 '18 at 00:22
@Dan The desired result is that I get all customers with matching `coupon code` in the items table, but not used the `coupon_new_code` — Undefined Variable, May 07 '18 at 00:22

Dan · Answer 1 · 2018-05-07T00:31:19.417

1

Try changing

LEFT JOIN to INNER JOIN (or just JOIN)

This will work to speed things up assuming that you only want to see orders that have both customers and items associated with them. Currently your query is trying to return all data from the order table but that's not needed. It's possible other changes to the database structure could improve things as well.

The top answer here provides a useful diagram that demonstrates the difference between these types of statements.

edited May 07 '18 at 00:31

answered May 07 '18 at 00:25

Dan

9,391
5
41
73

The is null condition implies the opposite – Strawberry May 07 '18 at 06:25
@Strawberry there is only one null condition `where items.coupon_new_code is null` I don't see how that will limit the results in the way OP is looking – Dan May 07 '18 at 13:35

score 1 · Answer 2 · answered May 07 '18 at 02:43

At the very least you need an index on coupon_code on both order and items tables. Consider also adding to a compound index, the other field you are joining on custid, as well as on your WHERE conditions items.coupon_new_code, order.status and order.promo_code. Knowing next to nothing about your data I can only speculate about what the dbms will use. Try various combinations in a compound key and run explain to see what's being used. It's really going to depend on the specificity of the data in your columns.

Posting the output of EXPLAIN along with the tables' schema will help us improve these answers.

MySQL Left join makes query very slow...how can I refactor?

2 Answers2