Re: INNER JOIN across multiple tables appear very slow.

[sam wun]

sam wun wrote:
Hi,
Can anyone tell me how to optimize the following sql statement?
$sql_1 = " SELECT t.prodcode 'Product Code',
                       t.prodname 'Product Name',
                       ROUND(avg(t.salesvolume),2) 'PastSales Quantity',
                       ROUND(avg(tt.salesvolume),2) 'Sales Quantity',
                       ROUND(avg(t.netsales),2) 'PastSales',
                       ROUND(avg(tt.netsales),2) 'Sales',
                       Round(((avg(t.netsales) - 
avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff',
                       Round(((avg(t.salesvolume) - 
avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff'
                       from customer c
               INNER JOIN transaction t
                       ON c.custcode = t.custcode
                       AND date(t.date) >= '$past_date_period_startdate'
                       AND date(t.date) <= '$past_date_period_enddate'
                       AND c.salescode = '$salescode_param'
               INNER JOIN transaction tt
                       ON c.custcode = tt.custcode
                       AND date(tt.date) >= 
'$input_date_period_startdate'
                       AND date(tt.date) <= '$input_date_period_enddate'
                       AND c.salescode = '$salescode_param'
                       AND t.prodcode = tt.prodcode
               INNER JOIN inventory i
                       ON i.prodcode = t.prodcode
                       AND i.prodcode = tt.prodcode
                       AND i.basename = '$basename_param'
               WHERE i.prodname is not NULL
                       and i.basename is not NULL
                 GROUP BY c.salescode, i.basename, t.prodcode
                 ORDER BY SalesDiff desc;

Thanks
Sam

Hi, here is the result from the explain command on the query:
mysql> explain SELECT i.basename, c.salescode,
   -> avg(t.salesvolume) 'PastSales Quantity',
   -> avg(tt.salesvolume) 'Sales Quantity',
   -> avg(t.netsales) PastSales,
   -> avg(tt.netsales) Sales,
   -> Round(((avg(t.netsales) - 
avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff',
   -> Round(((avg(t.salesvolume) - 
avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff', c.salescode
   -> from customer c
   -> INNER JOIN transaction t
   -> ON c.custcode = t.custcode
   -> AND date(t.date) >= '2003-01-22'
   -> AND date(t.date) <= '2004-01-22'
   -> INNER JOIN transaction tt
   -> ON c.custcode = tt.custcode
   -> AND date(tt.date) >= '2004-01-22'
   -> AND date(tt.date) <= '2005-01-22'
   -> INNER JOIN inventory i
   -> ON i.prodcode = t.prodcode
   -> AND i.prodcode = tt.prodcode
   -> WHERE i.prodname is not NULL
   -> and i.basename is not NULL
   -> group by c.salescode,i.basename
   -> order by c.salescode, 'SalesDiff' desc ;
+----+-------------+-------+--------+-------------------+----------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys     | key      | 
key_len | ref                 | rows | 
Extra                                        |
+----+-------------+-------+--------+-------------------+----------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | i     | ALL    | PRIMARY,basename  | NULL     | 
NULL    | NULL                | 2491 | Using where; Using temporary; 
Using filesort |
|  1 | SIMPLE      | t     | ref    | custcode,prodcode | prodcode | 
32      | datacube.i.prodcode |   30 | Using 
where                                  |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,custcode  | PRIMARY  | 
32      | datacube.t.custcode |    1 
|                                              |
|  1 | SIMPLE      | tt    | ref    | custcode,prodcode | prodcode | 
32      | datacube.i.prodcode |   30 | Using 
where                                  |
+----+-------------+-------+--------+-------------------+----------+---------+---------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]