MySQL未在子查询联接中使用索引

我使用此查询来汇总头寸的元素及其子元素，以检查价格是否正确。当我运行此查询时，我得到了正确的结果，但是对于我的元素表超过2M +行和子元素9M +行，它的工作时间将近2分钟。

我认为MySQL由于子查询上的联接而不能正确使用索引，但是我不知道如何解决这个问题。

SELECT `pos`.`id`,`pos`.`Name`,`pos`.`Mark`,`pos`.`Number`,`elem`.`elemPrice` as `elemPrice`,`elem`.`subelemPrice` as `subelemPrice`,`pos`.`price` as `price`,`elem`.`elemCalcPrice` as `elemCalcPrice`,`elem`.`subelemCalcPrice` as `subelemCalcPrice`,`pos`.`supplier_webcalcprice`
FROM `proposal_draft_positions` `pos`
LEFT JOIN (
SELECT SUM(quantity * price) as elemPrice,SUM(quantity * supplier_webcalcprice) as elemCalcPrice,SUM(quantity * subelem.subelemPrice) as subelemPrice,SUM(quantity * subelem.subelemCalcPrice) as subelemCalcPrice,position,id,quantity
FROM proposal_position_elements elm
LEFT JOIN (
SELECT SUM(price * quantity) as subelemPrice,SUM(supplier_webcalcprice * quantity) as subelemCalcPrice,element
FROM proposal_position_subelements
GROUP BY element) subelem ON subelem.element = elm.id
GROUP BY position) elem ON `elem`.`position` = `pos`.`id`
WHERE `draft_id` = 29407


EXPLAIN显示MySQL在连接上未正确使用索引：

表DDL：

create table proposal_draft_positions
(
id int(10) auto_increment
primary key,Number int(10) null,Mark varchar(200) null,Name json null,price decimal(10,2) default 0.00 not null,supplier_webcalcprice decimal(10,2) default 0.00 null,)
charset = utf8;
create table proposal_position_elements
(
id int(30) auto_increment primary key,position int(10) not null,quantity decimal(10,2) default 1.00 not null,2) default 0.00 null,constraint fk_proposal_position_elements
foreign key (position) references proposal_draft_positions (id)
on delete cascade
)
charset = utf8;
create index `positions-elements-fk`
on proposal_position_elements (position);
create table proposal_position_subelements
(
id int(50) auto_increment
primary key,element int(30) not null,2) default 1.00 not null,2) default 0.00 null,constraint fk_proposal_position_subelements
foreign key (element) references proposal_position_elements (id)
on delete cascade
)
charset = utf8;
create index `element-subelement-fk`
on proposal_position_subelements (element);


由于EXPLAIN显示已对整个表运行了JOIN的查询，因此我决定添加过滤功能，并很少重写查询：

SELECT `pos`.`id`,`pos`.`Name`,`pos`.`Mark`,`pos`.`Number`,`pos`.`supplier_webcalcprice`,`pos`.`price` as `price`,`elem`.`elemPrice` as `elemPrice`,`elem`.`elemCalcPrice` as `elemCalcPrice`,`elem`.`subelemPrice` as `subelemPrice`,`elem`.`subelemCalcPrice` as `subelemCalcPrice`
FROM `proposal_draft_positions` `pos`
LEFT JOIN (
SELECT SUM(elem.price * elem.quantity) as elemPrice,SUM(elem.supplier_webcalcprice * elem.quantity) as elemCalcPrice,SUM(elem.quantity * subelem.subelemPrice) as subelemPrice,SUM(elem.quantity * subelem.subelemCalcPrice) as subelemCalcPrice,elem.position,elem.id,elem.quantity
FROM `proposal_draft_positions` pdp
LEFT JOIN proposal_position_elements elem on pdp.id = elem.position
LEFT JOIN (
SELECT SUM(subelem.price * subelem.quantity) as subelemPrice,SUM(subelem.supplier_webcalcprice * subelem.quantity) as subelemCalcPrice,element
FROM `proposal_draft_positions` pdp
LEFT JOIN proposal_position_elements elem on pdp.id = elem.position
LEFT JOIN proposal_position_subelements subelem on elem.id = subelem.element
WHERE pdp.draft_id = 29407
GROUP BY element) subelem ON subelem.element = elem.id
WHERE pdp.draft_id = 29407
GROUP BY position) elem ON `elem`.`position` = `pos`.`id`
WHERE `draft_id` = 29407;