帮帮偶

[vickyli0307]

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范围的

[wwwwb]

5秒？速度不慢嘛，什么问题？

[ACMAIN_CHM]

提供以下信息。

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

[vickyli0307]

才30W就要5s还不慢啊，那数据一多怎么办啊

[vickyli0307]

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

[ACMAIN_CHM]

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

[vickyli0307]

没用啊，7S了

[vickyli0307]

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