查询很慢啊

Question

本帖最后由 vickyli0307 于 2009-11-30 14:24:52 编辑

Answer 1

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

Answer 2

+----+-------------+-------+------+---------------+------+---------+------------
-------+--------+---------------------------------+
| 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                     |
+----+-------------+-------+------+---------------+------+---------+------------
-------+--------+---------------------------------+

Answer 3

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

Answer 4

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 |
+----+-------------+----------+------+---------------+------+---------+------+--
------+-------------+

Answer 5

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