Thomas Moe February 2016

MySQL query take too long

MySQL server give no reply and hanged after the following query:

SELECT 
i.id AS id, 
i.name AS product, 
i.stock AS stock, 
IF(SUM(s1.qty) IS NULL, 0, SUM(s1.qty)) AS thisqty, 
IF(SUM(s2.qty) IS NULL, 0, SUM(s2.qty)) AS lastqty, 
IF(SUM(s3.qty) IS NULL, 0, SUM(s3.qty)) AS last2qty, 
IF(SUM(s4.qty) IS NULL, 0, SUM(s4.qty)) AS last3qty 
FROM 
item i, 
sale s1, sale s2, sale s3, sale s4, 
odr o1, odr o2, odr o3, odr o4 
WHERE 
i.id = s1.itemid AND s1.oui = o1.oui AND (o1.ddate BETWEEN '2016-02-08' AND '2016-02-14') AND 
i.id = s2.itemid AND s2.oui = o2.oui AND (o2.ddate BETWEEN '2016-02-01' AND '2016-02-07') AND 
i.id = s3.itemid AND s3.oui = o3.oui AND (o3.ddate BETWEEN '2016-01-25' AND '2016-01-31') AND 
i.id = s4.itemid AND s4.oui = o4.oui AND (o4.ddate BETWEEN '2016-01-18' AND '2016-01-24') GROUP BY PRODUCT;

My table structure are as follows:

mysql> describe item;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(3)       | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | NO   |     | NULL    |                |
| wprice  | int(11)      | NO   |     | NULL    |                |
| sprice  | int(11)      | NO   |     | NULL    |                |
| bprice  | int(11)      | NO   |     | NULL    |                |
| stock   | float        | NO   |     | NULL    |                |
| buyfrom | varchar(255) | NO   |     | NULL    |                |
| unit    | varchar(255) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
8 rows in set (0.03 sec)

mysql> describe odr;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| oui    | varchar(32) | YES  |     | NULL         

Answers


Code-Monk February 2016

Changed query to filter data inside the sub queries.

Try this:

SELECT 
    i.id AS id, 
    i.name AS product, 
    i.stock AS stock, 
    IF(SUM(s1.qty) IS NULL, 0, SUM(s1.qty)) AS thisqty, 
    IF(SUM(s2.qty) IS NULL, 0, SUM(s2.qty)) AS lastqty, 
    IF(SUM(s3.qty) IS NULL, 0, SUM(s3.qty)) AS last2qty, 
    IF(SUM(s4.qty) IS NULL, 0, SUM(s4.qty)) AS last3qty 
FROM  item i
inner join (select sale.* from sale 
                inner join odr on sale.oui=odr.oui 
                where odr.ddate BETWEEN '2016-02-08' AND '2016-02-14'
            ) s1 on i.id = s1.itemid 
inner join (select sale.* from sale 
                inner join odr on sale.oui=odr.oui 
                where odr.ddate BETWEEN '2016-02-01' AND '2016-02-07'
            ) s2 on i.id = s2.itemid 

inner join (select sale.* from sale 
                inner join odr on sale.oui=odr.oui 
                where odr.ddate BETWEEN '2016-01-25' AND '2016-01-31'
            ) s3 on i.id = s3.itemid 

inner join (select sale.* from sale 
                inner join odr on sale.oui=odr.oui 
                where odr.ddate BETWEEN '2016-01-18' AND '2016-01-24'
            ) s4 on i.id = s4.itemid             
GROUP BY PRODUCT;

Post Status

Asked in February 2016
Viewed 2,065 times
Voted 11
Answered 1 times

Search




Leave an answer