【新手求助】Mysql 查询优化，老项目了表结构不是很合理，但是不能随便改了，只能从sql改

码拜

9年 ago

EXPLAIN
SELECT
BATCHS.custtypedisc CUSTTYPEDISC,
min(BATCHS.Timesno) TIMESNO
FROM
S_SALE_FOLLOW SALE ,
A_CUST_BASEINFO CUST,
A_CUST_BATCH BATCHS
WHERE
CUST.CUSTID = SALE.CUSTID
AND BATCHS.TIMESNO = CUST.CUSTBATCHCODE
AND SALE.NAMEDELIVERTIME >=
“”2015-08-16 00:00:00″”
AND SALE.NAMEDELIVERTIME <=
“”2015-09-22 23:59:59″”
GROUP BY
batchs.TIMESNO
ORDER BY
batchs.TIMESNO DESC;
show INDEX from s_sale_follow;
show INDEX from a_cust_baseinfo;
show INDEX from a_cust_batch;

可以的话继续加分，还有其他的，用的left join 但是效率就是不高


SELECT 
b.custtypedisc CUSTTYPEDISC,
MIN(b.Timesno) TIMESNO
FROM
A_CUST_BATCH b
INNER JOIN
A_CUST_BASEINFO c
ON b.TIMESNO = c.CUSTBATCHCODE
INNER JOIN
(SELECT CUSTID FROM S_SALE_FOLLOW WHERE 
NAMEDELIVERTIME >=
""2015-08-16 00:00:00""
AND NAMEDELIVERTIME < 
""2015-09-23 00:00:00"") s
ON c.CUSTID = s.CUSTID
GROUP BY
b.TIMESNO
ORDER BY
b.TIMESNO DESC;