查询很慢啊

[vickyli0307]

select client,sum(fabricWt) total from jobs a,jobsheet b where a.jobno=b.jobno
and createDate>='2008-11-30' and createDate<'2009-12-01' group by client order by createDate


jobs:jobno,client 共10W条记录
jobsheet:jobsheetid,jobno,createdate, fabricwt 共10W条记录
查询花了竟5s

那位大哥帮帮忙哦

[vinsonshen]

用explain跟踪分析下吧
看看索引是否走得跟你想要的一样没有

[vickyli0307]

+----+-------------+-------+------+---------------+------+---------+------------
-------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------------
-------+--------+---------------------------------+
| 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL
| 100230 | Using temporary; Using filesort |
| 1 | SIMPLE | b | ref | dat,Index_3 | dat | 42 | inventory.a
.jobNo | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------------
-------+--------+---------------------------------+

[vickyli0307]

在jobsheet中索引有：primary(jid),dat(jobno),index_3(createdate)
在jobs中的索引有:primary(jobno)

[vickyli0307]

SELECT * FROM jobsheet where createDate>='2008-11-30' and createDate <'2009-12-01'
这个查询也要5s.共查到10W条记录， 在createdate上建有索引呢
用explain分析
+----+-------------+----------+------+---------------+------+---------+------+--
------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+--
------+-------------+
| 1 | SIMPLE | jobsheet | ALL | Index_3 | NULL | NULL | NULL | 100224 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+--
------+-------------+

[ACMAIN_CHM]

当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html