mysql为啥这样呢

Question

 这条语句执行了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.tidmysql> 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
           |
+----+-------------+----------+--------+------------------------------------+---
---------------+---------+------------------------+------+----------------------

Answer 1

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

Answer 2

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