CODEWITHSUNDEEP
mysqlsqldatabaseselectjoin
Jeremy
Jeremy

Reputation: 63

I need help speeding up this select statement

I'm looking for help speeding up this select statement. I need to do left joins on this statement which has caused it to really bog down. Doing a simple join (on any of the joins, especially the first two, really speeds things up). Using all LEFT JOINS takes about 33 seconds. Changing either of the first two to regular JOINS takes 0.2 seconds. Changing the last to a JOIN takes 0.8 seconds. Any of these quicker solutions doesn't give me all the results I need.

SELECT 
    i.id, i.part_number, v.name, i.description, sum(oi.quantity) as count, i.filename, "0"
FROM 
   inventory i 
LEFT JOIN 
   order_items_part_numbers o
   ON i.id = o.inventory_id 
LEFT JOIN order_items oi
  ON oi.id = o.order_items_id 
LEFT JOIN vendors v
  ON v.id = i.vendor_id 
WHERE 
   i.deleted = 0 GROUP BY i.part_number

Here are the indexes I have set up. enter image description here

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can try using a subquery:

SELECT i.*, v.name,
       (SELECT SUM(oi.quantity)
        FROM order_items_part_numbers o JOIN  
             order_items oi
             ON oi.id = o.order_items_id
        WHERE i.id = o.inventory_id
       ) as count, '0'
FROM inventory i LEFT JOIN
     vendors v
     ON v.id = i.vendor_id 
WHERE i.deleted = 0;

This gets rid of the outer aggregation, which should be a win.

Upvotes: 1

Related Questions