帮帮偶

Question

select c.description,c.description mon,sum(actualConsume)/1000  total from itemtypes c,inventories b ,jobItems a
where a.partid=b.partid and a.consumetime>='2008-12-13' and a.consumetime<'2009-12-16'
and c.tid=b.itemtype  group by b.itemType查询花了5s，
itemtypes 有1000, tid,description。索引tid
inventories 有266,partid,itemtype 索引partid
jobItems 30W条记录 consumetime,partid.索引partid,consumetime 其中consumetime的记录是从2009-1-1到2009-12-14范围的

Answer 1

提供以下信息。show index from itemtypes
show index from inventories
show index from jobItems和explain select c.description,c.description mon,sum(actualConsume)/1000  total from itemtypes c,inventories b ,jobItems a
where a.partid=b.partid and a.consumetime>='2008-12-13' and a.consumetime <'2009-12-16'
and c.tid=b.itemtype  group by b.itemType

Answer 2

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         |
      1964 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+mysql> show index from inventories ;
+-------------+------------+------------------+--------------+-------------+----
-------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name         | Seq_in_index | Column_name | Col
lation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+------------------+--------------+-------------+----
-------+-------------+----------+--------+------+------------+---------+
| inventories |          0 | PRIMARY          |            1 | partid      | A
       |         322 |     NULL | NULL   |      | BTREE      |         |
| inventories |          1 | Index_activeitem |            1 | activeItem  | A
       |           2 |     NULL | NULL   |      | BTREE      |         |
| inventories |          1 | Index_partno     |            1 | partNo      | A
       |         322 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+------------------+--------------+-------------+----
-------+-------------+----------+--------+------+------------+---------+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
     |      345277 |     NULL | NULL   |      | BTREE      |         |
| jobItems |          1 | Index_jobsheetid  |            1 | jobsheetid  | A
     |       86319 |     NULL | NULL   |      | BTREE      |         |
| jobItems |          1 | Index_consumed    |            1 | consumed    | A
     |           5 |     NULL | NULL   |      | BTREE      |         |
| jobItems |          1 | Index_partid      |            1 | partid      | A
     |         812 |     NULL | NULL   |      | BTREE      |         |
| jobItems |          1 | Index_consumetime |            1 | consumeTime | A
     |      345277 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+-------------------+--------------+-------------+------
-----+-------------+----------+--------+------+------------+---------++----+-------------+-------+--------+--------------------------------+----------
----+---------+----------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys                  | key
    | key_len | ref                  | rows | Extra                           |
+----+-------------+-------+--------+--------------------------------+----------
----+---------+----------------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ALL    | PRIMARY                        | NULL
    | NULL    | NULL                 |  322 | Using temporary; Using filesort |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY                        | PRIMARY
    | 3       | inventory.b.itemType |    1 |                                 |
|  1 | SIMPLE      | a     | ref    | Index_partid,Index_consumetime | Index_par
tid | 3       | inventory.b.partid   |  425 | Using where                     |
+----+-------------+-------+--------+--------------------------------+----------
----+---------+----------------------+------+---------------------------------+

Answer 3

建一个索引jobItems(partid,consumeTime) 应该就可以了。

Answer 4

怎么没人回答呢
我还试了建索引jobItems(consumeTime，partid)有点点效果
再次请求帮助哦