mysql - 如何避免对我的 MySQL 查询进行全表扫描

Question

谁能帮我修改这个查询

UPDATE APA_Pended_Demand 
SET APA_Pended_Demand.genericDemandId=( 
    SELECT APA_Generic_Demand_Details.genericDemandId  
    FROM APA_Generic_Demand_Details  
    WHERE APA_Generic_Demand_Details.demandID=APA_Pended_Demand.demandID  
    AND APA_Pended_Demand.isPend = 1 
    AND APA_Generic_Demand_Details.genericDemandId != ''  
    AND APA_Generic_Demand_Details.genericDemandId IS NOT NULL  
    AND APA_Pended_Demand.reactivateDate > UTC_TIMESTAMP()
    AND APA_Generic_Demand_Details.status < 300
    AND APA_Pended_Demand.ID BETWEEN 1 AND 10000

表的索引是

PRIMARY KEY (`id`),
 KEY `apgnricdemnddetils_bndnresn` (`abandonReason`),
 KEY `apgnricdmnddetils_prdcttype` (`productType`),
 KEY `apgnricdmnddtils_srcereqrle` (`sourceReqRole`),
 KEY `apgnericdemnddetils_methdin` (`methodIn`),
 KEY `IX1_APA_Generic_Demand_Details` (`status`,`customerReference`,`policyNo`,`genericDemandId`) USING BTREE,
 KEY `IX2_APA_Generic_Demand_Details` (`status`,`schemeReference`,`genericDemandId`) USING BTREE,
 KEY `IX3_APA_Generic_Demand_Details` (`status`,`aggregationKey`,`genericDemandId`) USING BTREE,
 KEY `IX4_APA_Generic_Demand_Details` (`status`,`initiator`,`aggregationKey`,`genericDemandId`) USING BTREE,
 KEY `IX5_APA_Generic_Demand_Details` (`status`,`policyNo`,`genericDemandId`) USING BTREE,
 KEY `IX7_APA_Generic_Demand_Details` (`genericDemandId`) USING BTREE,
 KEY `IX8_APA_Generic_Demand_Details` (`initiator`,`status`) USING BTREE, 

查询没有使用索引。我尝试解释查询，它显示类型为 All，键为 None。那么无论如何让它使用索引还是我们需要更改查询？

Answer 1

改为使用更新连接:

UPDATE APA_Pended_Demand t1
INNER JOIN APA_Generic_Demand_Details t2
    ON t1.demandID = t2.demandID
SET t1.genericDemandId = t2.genericDemandId
WHERE t1.isPend = 1 AND
      COALESCE(t2.genericDemandId, '') != '' AND
      t1.reactivateDate > UTC_TIMESTAMP() AND
      t2.status < 300 AND
      t1.ID BETWEEN 1 AND 10000