我想总结一下订单。我喜欢以下产品和订购项目：

DROP TABLE IF EXISTS p;
CREATE TABLE p (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `combine` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `combine`(`combine`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS i;
CREATE TABLE i (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `p` int(10) unsigned DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `p`(`p`)
) ENGINE=InnoDB;

INSERT INTO p SET id=1, combine=NULL;
INSERT INTO p SET id=2, combine=1;
INSERT INTO p SET id=3, combine=1;
INSERT INTO p SET id=4, combine=NULL;

INSERT INTO i SET id=1, p=1, quantity=5;
INSERT INTO i SET id=2, p=1, quantity=2;
INSERT INTO i SET id=3, p=2, quantity=1;
INSERT INTO i SET id=4, p=3, quantity=4;
INSERT INTO i SET id=5, p=4, quantity=2;
INSERT INTO i SET id=6, p=4, quantity=1;

这个想法是产品可以合并，这意味着所有的销售都是为这些产品合并的。例如，这意味着产品1、2和3应该有相同的结果：这些产品的所有销售额相加。所以我做了：

SELECT p.id, SUM(i.quantity) 
FROM p 
LEFT JOIN p AS p_all ON (p_all.id = p.id OR p_all.combine=p.combine OR p_all.id = p.combine OR p_all.combine = p.id)
LEFT JOIN i ON i.p = p_all.id
GROUP BY p.id;

得出所需结果：

p=1:    12 (i: 1, 2, 3, 4 added)
p=2:    12 (i: 1, 2, 3, 4 added)
p=3:    12 (i: 1, 2, 3, 4 added)
p=4:    3 (i: 5, 6 added)

我的问题是，在实际数据上，或在p\u combine的乘积的联接中，使得查询非常慢。只需不使用组合进行查询就需要0.2秒，而or会使查询持续30秒以上。
如何在mysql中提高查询效率？
补充：实际查询还有一些限制，如：

SELECT p.id, SUM(i.quantity) 
FROM p 
LEFT JOIN p AS p_all ON (p_all.id = p.id OR p_all.combine=p.combine OR p_all.id = p.combine OR p_all.combine = p.id)
LEFT JOIN i ON i.p = p_all.id
LEFT JOIN orders o ON o.id = i.order
WHERE o.ordered <= '2018-05-10'
AND i.flag=false
AND ...
GROUP BY p.id;

补充：解释真实数据：

+----+-------------+------------------+------------+-------+-----------------------------+---------+---------+--------------+------+----------+-------------------------------------------------+
| id | select_type | table            | partitions | type  | possible_keys               | key     | key_len | ref          | rows | filtered | Extra                                           |
+----+-------------+------------------+------------+-------+-----------------------------+---------+---------+--------------+------+----------+-------------------------------------------------+
|  1 | SIMPLE      | p                | NULL       | index | PRIMARY,...combine...       | PRIMARY | 4       | NULL         | 6556 |   100.00 | NULL                                            |
|  1 | SIMPLE      | p_all            | NULL       | ALL   | PRIMARY,combine             | NULL    | NULL    | NULL         | 6556 |   100.00 | Range checked for each record (index map: 0x41) |
|  1 | SIMPLE      | p                | NULL       | ref   | p                           | p       | 5       | p_all.id     |   43 |   100.00 | NULL                                            |
+----+-------------+------------------+------------+-------+-----------------------------+---------+---------+--------------+------+----------+-------------------------------------------------+