Hallo,
ich habe die Abfrage nun wie folgt:
SELECT SUM(FullPrice) AS total,
SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND (items_stock.locationID = '001' OR items_stock.locationID = '002' )
)
) AS stockValue,
SUM(
(SELECT SUM(items_stock.AvailableStock)
FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND items_stock.locationID = 'All'
)
) AS stockValueAvailable,
SUM(
(SELECT SUM(items_stock.Counter)/SUM(items_stock.Days)
FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND (items_stock.locationID = '001' OR items_stock.locationID = '002' )
)
) AS CounterDays,
IF( (SUM(IF(invoicelines.OrderQty > 0,invoicelines.OrderQty,0)) / ( SUM(IF(invoicelines.OrderQty > 0,invoicelines.OrderQty,0)) +
SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND (items_stock.locationID = '001' OR items_stock.locationID = '002' )
)
)
) * 100) IS NULL,0, (SUM(IF(invoicelines.OrderQty > 0,invoicelines.OrderQty,0)) / ( SUM(IF(invoicelines.OrderQty > 0,invoicelines.OrderQty,0)) +
SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND (items_stock.locationID = '001' OR items_stock.locationID = '002' )
)
)
) * 100)) AS salesRate,
IF((
(
SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND (items_stock.locationID = '001' OR items_stock.locationID = '002' )
)
)
) / ( SUM(IF(invoicelines.OrderQty > 0,invoicelines.OrderQty,0)) / ( 9 ) )
) IS NULL,0,(
(
SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND (items_stock.locationID = '001' OR items_stock.locationID = '002' )
)
)
) / ( SUM(IF(invoicelines.OrderQty > 0,invoicelines.OrderQty,0)) / ( 9 ) )
)) AS rangeDays,
SUM(items.buyQty) AS buyQty,
SUM(
(SELECT SUM(items_stock.PhysicalStock * items.attr_cost) FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND (items_stock.locationID = '001' OR items_stock.locationID = '002' )
)
) AS costs,
SUM(
(SELECT SUM(items_stock.PhysicalStock * items.PriceVK) FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND (items_stock.locationID = '001' OR items_stock.locationID = '002' )
)
) AS valueGoods,
(TO_DAYS(NOW())-TO_DAYS(items.created_at)) AS onlineDays,
( (
SUM(
(SELECT SUM(items_stock.PhysicalStock)
FROM items_stock
WHERE items_stock.ItemNrInt = items.ItemNrInt AND (items_stock.locationID = '001' OR items_stock.locationID = '002' )
)
)
) -
(
0 *
(
SUM(IF(invoicelines.OrderQty > 0,invoicelines.OrderQty,0)) / ( 9 )
)
)
) AS stockRangeDays,
items.attr_image, items.ItemNrInt, items.Parent, items.ItemNrSuppl, items.ItemNrSupplProduct,
SUM(IF(invoicelines.OrderQty > 0,invoicelines.OrderQty,0)) AS QtyTotal,
SUM(IF(invoicelines.OrderQty > 0 && ( invoicelines.OrderDate >= items.attr_special_from_date && items.attr_special_from_date != '0000-00-00' && (items.attr_special_to_date <= invoicelines.OrderDate || items.attr_special_to_date = '0000-00-00') ),invoicelines.OrderQty,0)) AS QtyTotalInSale,
(SELECT SUM(IF(invoicelines.OrderQty > 0,invoicelines.FullPrice*invoicelines.OrderQty,0)) ) AS colPriceTotal,
SUM(IF(invoicelines.OrderQty > 0 && ( invoicelines.OrderDate >= items.attr_special_from_date && items.attr_special_from_date != '0000-00-00' && (items.attr_special_to_date <= invoicelines.OrderDate || items.attr_special_to_date = '0000-00-00') ),(invoicelines.OrderQty * invoicelines.ItemPriceNoVAT)*20/100,0)) AS SumTotalInSale,
SUM(IF(invoicelines.OrderQty > 0,(
IF(
( (invoicelines.OrderDate <= items.attr_special_from_date) || (items.attr_special_from_date = '0000-00-00') ),(items.attr_royalties * invoicelines.OrderQty),
IF(
( invoicelines.OrderDate >= items.attr_special_from_date && (items.attr_special_to_date <= invoicelines.OrderDate || items.attr_special_from_date = '0000-00-00') ),ROUND((invoicelines.ItemPriceNoVAT * invoicelines.OrderQty)/5,10), 0
)
)
),0)) AS royalties,
SUM(IF(invoicelines.OrderQty > 0,invoicelines.FullPrice,0)) AS PriceTotal, items.ItemName AS ItemName
FROM items
LEFT JOIN attr_saison ON (items.attr_saison = attr_saison.ID OR items.attr_saison IS NULL)
LEFT JOIN ( SELECT DISTINCT ArtName,ItemNrInt,SUM(OrderQty) AS OrderQty,SUM(FullPrice) AS FullPrice,OrderDate,SUM(ItemPriceNoVAT) AS ItemPriceNoVAT,Status
FROM invoicelines WHERE ( OrderQty > '0' AND status = 'AUS' AND invoicelines.attr_department = '6' AND ( invoicelines.OrderDate >= '2012-02-01 00:00:00' AND invoicelines.OrderDate <= '2012-02-09 23:59:59' ))
GROUP BY ItemNrInt
ORDER BY NULL ) invoicelines ON (invoicelines.ItemNrInt = items.ItemNrInt)
WHERE items.attr_department = '6' GROUP BY items.Brand HAVING stockValue > '0'
Ich habe also die Attribute für department + gender mit in die Tabelle invoicelines gepackt ... das hat ziemlich viel gebracht, da nun die benötigten Zeilen daraus vorher reduziert werden.
+----+--------------------+--------------+-------+------------------------------+-----------------+---------+---------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------+-------+------------------------------+-----------------+---------+---------------------------------+-------+---------------------------------+
| 1 | PRIMARY | items | ref | attr_department | attr_department | 4 | const | 25831 | Using temporary; Using filesort |
| 1 | PRIMARY | attr_saison | index | PRIMARY | PRIMARY | 4 | NULL | 8 | Using index |
| 1 | PRIMARY | <derived13> | ALL | NULL | NULL | NULL | NULL | 1963 | |
| 13 | DERIVED | invoicelines | range | OrderQty,OrderDate,idx_speed | idx_speed | 12 | NULL | 19056 | Using where; Using temporary |
| 12 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 11 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
| 10 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
| 9 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
| 8 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
| 7 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
| 6 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
| 5 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
| 4 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
| 3 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
| 2 | DEPENDENT SUBQUERY | items_stock | ref | ItemNrInt | ItemNrInt | 4 | pixi_imperial_2.items.ItemNrInt | 8 | Using where |
+----+--------------------+--------------+-------+------------------------------+-----------------+---------+---------------------------------+-------+---------------------------------+
Einzig und allein die Suche nach "allem" gruppiert nach Brand ist immer noch der Overkill - hier geht momentan gar nichts :)
Prinzipiell soll die Suche das schnellste sein - die Inserts + Updates sind eher nebensache, die laufen des nachts per Cron in die Tabellen.
Eine Frage zum Gruppieren habe ich noch:
Der SELECT sollte eigentlich nur die Items holen, die GROUP BY items.Brand HAVING stockValue > '0' sind ... stockValue bezieht sich dabei auf das schon gruppierte Ergebnis.
Die Summierung der OrderQty aus den invoicelines kommt von:
SUM(IF(invoicelines.OrderQty > 0,invoicelines.OrderQty,0)) AS QtyTotal,
Kann ich in so einer SUM() auch mit HAVING arbeiten oder anderweitig das Ergebnis von stockValue verwenden?
Theoertisch sollte es so sein:
SUM(IF(invoicelines.OrderQty > 0 AND stockValue > 0,invoicelines.OrderQty,0)) AS QtyTotal
Danke nochmals
Mark