MySQL Forums
Forum List  »  German

Re: Performance bei SELECT
Posted by: Mark Knochen
Date: February 08, 2012 09:52PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2602
January 19, 2012 07:17AM
1451
January 19, 2012 12:59PM
1519
January 20, 2012 05:33AM
1734
January 20, 2012 06:10AM
1462
January 20, 2012 07:06AM
1477
January 20, 2012 12:11PM
1438
January 25, 2012 04:11AM
1303
January 25, 2012 04:24AM
1356
January 25, 2012 04:30AM
1282
January 25, 2012 04:49AM
1118
January 25, 2012 06:05AM
1408
January 25, 2012 06:37AM
1386
January 25, 2012 07:46AM
1457
January 26, 2012 12:57AM
1362
January 25, 2012 11:11PM
1236
January 25, 2012 11:50PM
1141
January 27, 2012 08:27AM
1538
January 27, 2012 06:21AM
1377
February 02, 2012 10:06PM
1085
February 03, 2012 05:34AM
1340
February 03, 2012 02:04PM
Re: Performance bei SELECT
1270
February 08, 2012 09:52PM
1317
February 09, 2012 08:41AM
1226
February 10, 2012 02:03AM
1479
February 10, 2012 02:52AM
1372
February 10, 2012 04:51AM
1341
February 09, 2012 11:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.