MySQL查询时间太长

Question

MySQL 服务器在执行以下查询后没有回复并挂起:

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;

我的表结构如下:

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    |       |
| odate  | varchar(16) | YES  |     | NULL    |       |
| ddate  | varchar(16) | YES  |     | NULL    |       |
| otime  | varchar(16) | YES  |     | NULL    |       |
| cid    | varchar(6)  | YES  |     | NULL    |       |
| disc   | int(11)     | YES  |     | NULL    |       |
| total  | int(11)     | YES  |     | NULL    |       |
| net    | int(11)     | YES  |     | NULL    |       |
| status | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
9 rows in set (0.02 sec)

mysql> describe sale;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| oui    | varchar(32) | YES  |     | NULL    |       |
| itemid | varchar(3)  | YES  |     | NULL    |       |
| qty    | float       | YES  |     | NULL    |       |
| price  | int(11)     | YES  |     | NULL    |       |
| amount | int(11)     | YES  |     | NULL    |       |
| profit | float       | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.03 sec)

我尝试使用 s1 进行查询，结果成功了。 where 子句中的 AND 可能过多。我也尝试过使用 phpmyadmin 但仍然不起作用

Answer 1

更改了查询以过滤子查询内的数据。

试试这个:

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;