我刚刚发现如果是按照6个表那样查询,还可以。查询慢的原因是,得到6个表的结果后,还要foreach循环,把itemNum的值传入到一个函数,可能是由于这个函数里面的SQL语句才慢的 EXPLAIN SELECT sum( com_cn_chuku_detail.quantity ) AS quantity FROM com_cn_chuku_detail JOIN com_cn_chuku ON com_cn_chuku.chukuNum = com_cn_chuku_detail.chukuNum WHERE com_cn_chuku.state =2 AND com_cn_chuku.submit_date BETWEEN '2012-1-1' AND '2012-06-15' AND com_cn_chuku_detail.itemNum =1012441 GROUP BY Date( com_cn_chuku.submit_date ) ORDER BY NULL
刚刚建立联合索引。 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE com_cn_chuku ref PRIMARY,state state 1 const 118 Using where; Using temporary 1 SIMPLE com_cn_chuku_detail ref chukuNum,itemNum chukuNum 4 com_backup.com_cn_chuku.chukuNum 47 Using where
问题解决了,原来是 EXPLAIN SELECT sum( com_cn_chuku_detail.quantity ) AS quantity FROM com_cn_chuku_detail JOIN com_cn_chuku ON com_cn_chuku.chukuNum = com_cn_chuku_detail.chukuNum WHERE com_cn_chuku.state =2 AND com_cn_chuku.submit_date BETWEEN '2012-1-1' AND '2012-06-15' AND com_cn_chuku_detail.itemNum =1012441 GROUP BY Date( com_cn_chuku.submit_date ) ORDER BY NULL com_cn_chuku_detail.itemNum =1012441没有加单引号,加了单引号,就OK了。
EXPLAIN SELECT sum( com_cn_chuku_detail.quantity ) AS quantity
FROM com_cn_chuku_detail
JOIN com_cn_chuku ON com_cn_chuku.chukuNum = com_cn_chuku_detail.chukuNum
WHERE com_cn_chuku.state =2
AND com_cn_chuku.submit_date
BETWEEN '2012-1-1'
AND '2012-06-15'
AND com_cn_chuku_detail.itemNum =1012441
GROUP BY Date( com_cn_chuku.submit_date )
ORDER BY NULL
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE com_cn_chuku ref PRIMARY,state state 1 const 118 Using where; Using temporary
1 SIMPLE com_cn_chuku_detail ref chukuNum,itemNum chukuNum 4 com_backup.com_cn_chuku.chukuNum 47 Using where
整体查询起来,还是没有多大改善。
可能因为6表得到的数组还要foreach循环一次,把里面的itemNum字段的值传入到一个函数。
这个函数也是根据不同的itemNum执行一个SQL语句,所以才会慢。如果这个函数要执行的SQL语句建立存储过程会不会快一点呢。
EXPLAIN SELECT sum( com_cn_chuku_detail.quantity ) AS quantity
FROM com_cn_chuku_detail
JOIN com_cn_chuku ON com_cn_chuku.chukuNum = com_cn_chuku_detail.chukuNum
WHERE com_cn_chuku.state =2
AND com_cn_chuku.submit_date
BETWEEN '2012-1-1'
AND '2012-06-15'
AND com_cn_chuku_detail.itemNum =1012441
GROUP BY Date( com_cn_chuku.submit_date )
ORDER BY NULL
com_cn_chuku_detail.itemNum =1012441没有加单引号,加了单引号,就OK了。