CODEWITHSUNDEEP
mysql
Ruudt
Ruudt

Reputation: 262

MySQL efficient lookup query

I have two tables that I need to combine to get the data I need, but the queries that give the desired result are all very expensive.

I have a products table (id, name) and a stock table (id, pid, supplier, stock, price). The stock.pid is the foreign key to products.id, but each product can have multiple suppliers and therefor multiple entries in the stock table.

What I need is for each product the cheapest price and current stock, combined with all data from the products record, ordered on prices ascending.

What I tried is (and several variations):

SELECT DISTINCT(pid), MIN(price), stock, p.*
FROM stock s LEFT (INNER) JOIN
     product
     ON pid = s.id
GROUP BY pid
ORDER BY price (LIMIT 100)

Upvotes: 0

Views: 116

Answers (3)

Ruudt
Ruudt

Reputation: 262

I've solved the problem as follows, also adding an extra level where the current time is taken into account because some suppliers cannot deliver on time after a certain time of day. What I did not include is a sum(stock). I've added that field to the products table as sumStock and always update it whenever a stock record is changed.

SELECT    sp.price
,         p.*
FROM      products p 
LEFT JOIN stock_products sp
USING     (ean)
WHERE     sp.id = IFNULL(
          (SELECT    psi.id
           FROM      stock_products psi 
           JOIN      suppliers sup
           ON        psi.pid = sup.id
           WHERE     psi.ean = p.ean
           AND       psi.stock > 0
           ORDER BY  IF(sup.time>40667, sup.deliveryTime, sup.deliveryTime+86400)
           ,         price
           LIMIT 1)
          ,
          (SELECT    psi.id
           FROM      stock_products psi 
           JOIN      suppliers sup
           ON        psi.pid = sup.id
           WHERE     psi.ean = p.ean
           ORDER BY  IF(sup.time>40667, sup.deliveryTime, sup.deliveryTime+86400)
           ,         price
           LIMIT 1)
ORDER BY  price

Note that I use the IFNULL for the rare cases a product has no stock. I still want to show those records. The query is a bit shorter without, but this is the complete solution I ended up using.

Also, 40667 is a number representing the current time. It should be whatever the current number of seconds since midnight is.

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

Um

Select products.*,stock.stock,cheapest.cheapestprice
From (select id,Min(Price) as cheapestPrice From Stock Group By id) cheapest
inner join Stock on stock.id = cheapest.id
inner join products on product.id = stock.pid
Order by cheapest.cheapestprice

maybe

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You want to do this with a subquery:

select p.*, sumstock, minprice
from products p left outer join
     (select s.pid, sum(stock) as sumstock, min(price) as minprice
      from stock s
      group by s.pid
     ) sp
     on sp.pid = p.id
order by minprice

Upvotes: 0

Related Questions