VERY complicated SQL query to optimize (subqueries, unions, pivot...) Help please

[Megadonk]

hi guys,

 

need help from the mysql optimization pros here. To the problem: I have two tables that store shipment tours of products. one is filled automatically, one by the user. What I need to do is to merge these two tables so that they can be sorted, searched etc. The tricky part is, that for each tour the products that are shipped have to be in the same result row.

What I have so far is this (i've narrowed it down to the essentials but it's still quite long):

EXPLAIN
SELECT
tableA_id, 
tableB_id, 
tour_number, 
tour_name, 
prod1_out,
prod1_in,
prod2_out,
prod2_in
FROM 
(
  (
    SELECT
    tableC.tableA_id, 
    tableC.tableB_id, 
    tableC.tour_number, 
    tableC.tour_name,
    SUM(CASE product_name WHEN 'product1' THEN out ELSE 0 END) as 'prod1_out',
    SUM(CASE product_name WHEN 'product1' THEN in ELSE 0 END) as 'prod1_in'
    SUM(CASE product_name WHEN 'product2' THEN out ELSE 0 END) as 'prod2_out',
    SUM(CASE product_name WHEN 'product2' THEN in ELSE 0 END) as 'prod2_in'
    FROM 
    (
      SELECT
      null as tableA_id,
      tableB.tableB_id,
      tableB.tour_number, 
      tableB.tour_name, 
      tableD.product_name, 
      tableC.out,
      tableC.in
      FROM
      tableB
      JOIN tableC ON tableC.tableB_id=tableB.tableB_id  
      JOIN tableD ON tableC.tableD_id=tableD.tableD_id
    )AS tableC GROUP BY tableB_id 
  )
  UNION ALL
  (
    SELECT
    tableC.tableA_id, 
    tableC.tableB_id, 
    tableC.tour_number, 
    tableC.tour_name, 
    SUM(CASE product_name WHEN 'product1' THEN out ELSE 0 END) as 'prod1_out',
    SUM(CASE product_name WHEN 'product1' THEN in ELSE 0 END) as 'prod1_in'
    SUM(CASE product_name WHEN 'product2' THEN out ELSE 0 END) as 'prod2_out',
    SUM(CASE product_name WHEN 'product2' THEN in ELSE 0 END) as 'prod2_in'
    FROM 
    (
      SELECT
      tableA.tableA_id,
      null as tableB_id,
      tableA.tour_number, 
      tableA.tour_name, 
      tableD.product_name, 
      tableC.out,
      tableC.in
      FROM
      tableA
      JOIN tableC ON tableC.tableA_id=tableA.tableA_id  
      JOIN tableD ON tableC.tableD_id=tableD.tableD_id 
    )AS tableC GROUP BY tableA_id 
  )
) as tableE ORDER BY tour_number DESC limit 0, 30

 

explain returns this:

id    select_type 	table 	    type 	  possible_keys 	          key 	      key_len 	 ref 	               rows 	Extra
1 	  PRIMARY 	    <derived2> 	ALL 	  NULL 	                    NULL 	      NULL 	     NULL 	             9776 	Using filesort
2 	  DERIVED 	    <derived3> 	ALL 	  NULL 	                    NULL 	      NULL 	     NULL 	               15 	Using temporary; Using filesort
3 	  DERIVED 	    tableB 	    ALL 	  PRIMARY 	                NULL 	      NULL 	     NULL 	                3 	 
3 	  DERIVED 	    tableC 	    ref 	  tableB_id,tableD_id 	    tableB_id 	5 	       tableB.tableB_id	      1 	Using where
3 	  DERIVED 	    tableD 	    eq_ref 	PRIMARY 	                PRIMARY 	  4 	       tableC.tableD_id 	    1 	 
4 	  UNION 	      <derived5> 	ALL 	  NULL 	                    NULL 	      NULL 	     NULL 	            29319 	Using temporary; Using filesort
5 	  DERIVED 	    tableD 	    ALL 	  PRIMARY 	                NULL 	      NULL 	     NULL 	                5 	 
5 	  DERIVED 	    tableC 	    ref 	  tableD_id,tableA_id 	    tableD_id 	4 	       tableD.tableD_id 	 5867 	 
5 	  DERIVED 	    tableA 	    eq_ref 	PRIMARY 	                PRIMARY 	  4 	       tableC.tableA_id	      1 	 
NULL 	UNION RESULT 	<union2,4> 	ALL 	  NULL 	                    NULL 	      NULL 	     NULL                NULL 

 

the indices i've chosen for the tables are:

tableA:  PRIMARY    PRIMARY tableA_id

tableB:  PRIMARY    PRIMARY tableB_id

tableC:  PRIMARY    PRIMARY tableC_id

        tableB_id  index  tableB_id

        tableD_id  index  tableD_id, tableA_id

tableD:  PRIMARY    PRIMARY tableD_id

 

I've execution times of about 2 seconds and my boss wants it to be faster...

The main problem seems to be, that i can't limit the inner most queries because i need them all to perform the grouping for the pivot table construction...

 

I need help on this please...

 

thanks alot! Mega

 

P.S. My Server version is 5.0.4

[fenway]

Confirm that each of the inner queries runs as fast as possible first.