太慢了，帮忙提醒一下

Question

select sum(c.actualConsume)/1000 as actualConsume  FROM inventories a left join ( select * from jobitems  where  consumetime>'2009-12-1' ) as c on a.partid=c.partid and c.consumed=1 where 1=1  and activeItem=true group by a.partid order by partno ascjobitems 共5W条记录，都是从2009-12-1号开始建立的，查询结果要6s，太慢了
inventories 共157条记录。
这是为了查询本月开始的消耗情况。因为实际情况这个数据投入到工厂使用，一个月会有5W条记录。太慢了

Answer 1

为什么不把 jobitems 表直接提出来？贴一下你的 show index from xxx
explain select 

Answer 2

select sum(c.actualConsume)/1000 as actualConsume  FROM inventories a 
left  join  jobitems   as c on a.partid=c.partid and c.consumed=1 where 1=1  and activeItem=true and c.consumetime>'2009-12-1' group by a.partid order by partno asc

Answer 3

初步你可以先试一下这个。需要索引
jobitems(consumetime)
inventories(partid)select sum(c.actualConsume)/1000 as actualConsume 
from inventories a left join (select partid from jobitems where consumetime>'2009-12-1' and c.consumed=1 ) as c
on a.partid=c.partid 
where 1=1 
and a.activeItem=true 
group by a.partid 

Answer 4

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
     |       59164 |     NULL | NULL   |      | BTREE      |         |
| jobitems |          1 | Index_consumetime |            1 | consumeTime | A
     |        2113 |     NULL | NULL   | YES  | BTREE      |         |
| jobitems |          1 | Index_consumed    |            1 | consumed    | A
     |           2 |     NULL | NULL   |      | BTREE      |         |
| jobitems |          1 | index_jobsheetid  |            1 | jobsheetid  | A
     |        9860 |     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
       |         123 |     NULL | NULL   |      | BTREE      |         |
| inventories |          1 | Index_activeitem |            1 | activeItem  | A
       |           2 |     NULL | NULL   |      | BTREE      |         |
| inventories |          1 | Index_partno     |            1 | partNo      | A
       |         123 |        5 | NULL   |      | BTREE      |         |
+-------------+------------+------------------+--------------+-------------+----
-------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)consumtime 用不上索引，因为符合条件的就是当月全部的。运行一个月就有这么多符合条件的结果

Answer 5

2楼的方法对于jobiitems这个表
刚开始确实有效，
但当jobitems有几十万条而其中符合2009-12的
只是其中的5W条时，就没用了

Answer 6

没有 看到你的记录，你将LEFT JOIN-》INNER JOIN，要快一些，不知道你要的结果你在jobitems表的partid 、consumed上建立复合索引试试

Answer 7

如果activeItem是a表的字段的话，IMHO: SELECT x.actualConsume
   FROM inventories a,
        (SELECT sum(c.actualConsume) / 1000 as actualConsume,
                partid
           FROM jobitems
          WHERE consumetime > '2009-12-1'
            AND consumed = 1
          GROUP BY partid) x
  WHERE a.partid = x.partid
    AND a.activeItem = true
  UNION ALL
 SELECT 0
   FROM inventories a
  WHERE a.partid = x.partid
    AND a.activeItem = true
    AND NOT EXISTS (SELECT 1 FROM jobitems c WHERE a.partid = c.partid AND c.consumed = 1)
    AND a.activeItem = true

Answer 8

后面那个 = true多了，order by也漏了

Answer 9

mysql> describe jobitems;
+---------------+-----------------------+------+-----+---------+----------------
+
| Field         | Type                  | Null | Key | Default | Extra
|
+---------------+-----------------------+------+-----+---------+----------------
+
| jobsheetid    | mediumint(6) unsigned | NO   | MUL | 0       |
|
| step          | tinyint(3) unsigned   | NO   |     | 0       |
|
| dosage        | double                | NO   |     | NULL    |
|
| partid        | mediumint(6) unsigned | NO   |     | NULL    |
|
| consumed      | tinyint(1)            | NO   | MUL | 0       |
|
| dosageRate    | decimal(10,4)         | NO   |     | NULL    |
|
| consumeTime   | datetime              | YES  | MUL | NULL    |
|
| cost          | decimal(10,2)         | YES  |     | NULL    |
|
| jobitemid     | int(10) unsigned      | NO   | PRI | NULL    | auto_increment
|
| actualConsume | double                | NO   |     | 0       |
|
+---------------+-----------------------+------+-----+---------+----------------mysql> describe inventories;
+-----------------+-----------------------+------+-----+-------------------+----
-------------------------+
| Field           | Type                  | Null | Key | Default           | Ext
ra                       |
+-----------------+-----------------------+------+-----+-------------------+----
-------------------------+
| partid          | mediumint(6) unsigned | NO   | PRI | NULL              | aut
o_increment              |
| partNo          | char(15)              | NO   | MUL | NULL              |
                         |
| description     | char(25)              | NO   |     | NULL              |
                         |
| qty_on_hand     | double                | NO   |     | 0                 |
                         |
| itemType        | mediumint(6) unsigned | NO   |     | NULL              |
                         |
| avg_cost        | decimal(10,2)         | NO   |     | 0.00              |
                         |
| barcode         | varchar(20)           | NO   |     | NULL              |
                         |
| activeItem      | tinyint(1)            | NO   | MUL | 0                 |
                         |
| create_date     | timestamp             | NO   |     | CURRENT_TIMESTAMP | on
update CURRENT_TIMESTAMP |
| specificGravity | double                | NO   |     | 0                 |
                         |
| unit            | enum('%','g/l')       | NO   |     | NULL              |
                         |
| qty_on_order    | double                | NO   |     | 0                 |
                         |
| min_stock_Level | double                | NO   |     | 0                 |
                         |
| purchase_wt     | double                | YES  |     | NULL              |
                         |
| packageUnit     | tinyint(3) unsigned   | NO   |     | 1                 |
                         |
+-----------------+-----------------------+------+-----+-------------------+----
-------------------------+

Answer 10

left join 一定要用啊，因为我是想查询jobitems里消耗时间哦2009-12-1的 partid，是在inventory 里的一部分的partid

Answer 11

我的union all的方式可行吗，就是先取左右表能够关联的数据，再union all所有左表中关联不到的数据

Answer 12

你的jobitems的索引没有partid???

Answer 13

我用explain发现的，只要主表有索引就可以，查询行一样的