mysql为啥这样呢

[vickyli0307]

这条语句执行了26s,请帮忙看看
select b.description,b.description mon,sum(jobitems.actualConsume*jobitems.cost)/1000 total
from jobitems ,jobsheet,itemTypes a,itemTypes b,jobs where jobsheet.jid = jobitems.jobsheetid
and jobsheet.jobNo=jobs.jobNo and a.tid = jobs.fabricType and a.pid=b.tid and
jobitems.consumeTime>=' 2008-11-30' and jobitems.consumeTime<'2009-12-01' group by mon,b.tid

mysql> show index from jobitems;
+----------+------------+-------------------+--------------+-------------+------
-----+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Colla
tion | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-------------------+--------------+-------------+------
-----+-------------+----------+--------+------+------------+---------+
| jobitems | 0 | PRIMARY | 1 | jobitemid | A
| 600193 | NULL | NULL | | BTREE | |
| jobitems | 1 | Index_jobsheetid | 1 | jobsheetid | A
| 300096 | NULL | NULL | | BTREE | |
| jobitems | 1 | Index_consumed | 1 | consumed | A
| 10 | NULL | NULL | | BTREE | |
| jobitems | 1 | Index_consumetime | 1 | consumeTime | A
| 600193 | NULL | NULL | YES | BTREE | |
| jobitems | 1 | Index_partid | 1 | partid | A
| 524 | NULL | NULL | | BTREE | |
+----------+------------+-------------------+--------------+-------------+------
-----+-------------+----------+--------+------+------------+---------+

mysql> show index from jobs;
+-------+------------+---------------+--------------+-------------+-----------+-
------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------+--------------+-------------+-----------+-
------------+----------+--------+------+------------+---------+
| jobs | 0 | PRIMARY | 1 | jobNo | A |
100230 | NULL | NULL | | BTREE | |
| jobs | 1 | FK_jobs_1 | 1 | color | A |
100230 | NULL | NULL | | BTREE | |
| jobs | 1 | FK_jobs_2 | 1 | fabricType | A |
1207 | NULL | NULL | | BTREE | |
| jobs | 1 | Index_dyetype | 1 | dyetype | A |
4 | NULL | NULL | | BTREE | |
+-------+------------+---------------+--------------+-------------+-----------+-
------------+----------+--------+------+------------+---------+

mysql> show index from jobsheet;
+----------+------------+-------------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-------------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
| jobsheet | 0 | PRIMARY | 1 | jid | A |
100224 | NULL | NULL | | BTREE | |
| jobsheet | 1 | Index_3 | 1 | createDate | A |
100224 | NULL | NULL | | BTREE | |
| jobsheet | 1 | Index_jobno | 1 | jobNo | A |
100224 | 10 | NULL | | BTREE | |
+----------+------------+-------------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+

mysql> show index from itemtypes;
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | C
ardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
| itemtypes | 0 | PRIMARY | 1 | tid | A |
566 | NULL | NULL | | BTREE | |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+

mysql> explain select b.description,b.description mon,sum(jobitems.actualConsume
*jobitems.cost)/1000 total
-> from jobitems ,jobsheet,itemTypes a,itemTypes b,jobs where jobsheet.jid
= jobitems.jobsheetid
-> and jobsheet.jobNo=jobs.jobNo and a.tid = jobs.fabricType and a.pid=b.ti
d and
-> jobitems.consumeTime>=' 2008-11-30' and jobitems.consumeTime<'2009-12-01
' group by mon,b.tid;
+----+-------------+----------+--------+------------------------------------+---
---------------+---------+------------------------+------+----------------------
-----------+
| id | select_type | table | type | possible_keys | ke
y | key_len | ref | rows | Extra
|
+----+-------------+----------+--------+------------------------------------+---
---------------+---------+------------------------+------+----------------------
-----------+
| 1 | SIMPLE | a | ALL | PRIMARY | NU
LL | NULL | NULL | 566 | Using temporary; Usin
g filesort |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PR
IMARY | 3 | inventory.a.pid | 1 |
|
| 1 | SIMPLE | jobs | ref | PRIMARY,FK_jobs_2 | FK
_jobs_2 | 3 | inventory.a.tid | 83 | Using index
|
| 1 | SIMPLE | jobsheet | ref | PRIMARY,Index_jobno | In
dex_jobno | 30 | inventory.jobs.jobNo | 1 | Using where
|
| 1 | SIMPLE | jobitems | ref | Index_jobsheetid,Index_consumetime | In
dex_jobsheetid | 3 | inventory.jobsheet.jid | 2 | Using where
|
+----+-------------+----------+--------+------------------------------------+---
---------------+---------+------------------------+------+----------------------

[storyxsj]

1.看到了这个 Using temporary; Using filesort ,加大sort_buffer再试试？
2.看到了,from .. itemTypes a,itemTypes b where ...and a.pid=b.tid 这两张表一样啊，考虑是否有必要这样子

[vickyli0307]

谢谢^—^
sort_buffer是对myisam吧
我的是innodb
我试了也没用
我的innodb_buffer_pool_size=155M
关于两表一样是因为内连接