嵌套查询的查询优化mysql

Question

我无法知道如何优化这个查询，请注意t_flowhistory表有16500行，下面的查询只是不执行，但是在较小的数据库上触发时，相同的查询工作得很好。有什么方法可以优化这个查询吗？

SELECT t_flowhistory.a_productid, t_flowhistory.a_torole, t_product.a_reference, t_flowhistory.a_assigneddate FROM ((select * from t_flowhistory WHERE a_flowhistoryid in (SELECT max(a_flowhistoryid) FROM t_flowhistory GROUP by a_productid)) as t_flowhistory) INNER JOIN t_product ON t_product.a_productid = t_flowhistory.a_productid WHERE (t_flowhistory.a_status like 'Assigned' or t_flowhistory.a_status like 'rejected') and t_flowhistory.a_isresolved = '1' and t_product.a_active = 0 and t_product.a_ispublished=0 and t_flowhistory.a_torole = 2 ORDER BY t_flowhistory.a_assigneddate desc

用于t_flowhistory的表结构：

列类型为空默认值

a_flowhistoryid (小学) bigint(20) No

a_productid bigint(20)是NULL

a_fromuserid int(10)是NULL

a_fromrole int(10)是NULL

a_torole int(10)是NULL

a_status枚举(“指定”、“移动”、“完成”、“拒绝”)

a_isresolved枚举(‘0’，'1')是1

a_reasonid int(10)是NULL

a_remarks varchar(250)是NULL

a_assigneddate日期时间是NULL

t_products的表结构

列类型为空默认值

a_productid (小学) int(11) No

a_reference varchar(42)是NULL

a_price十进制(20，6)是0.000000

a_defaultcategoryid int(10)是0

a_sequence int(10) Yes 100000

a_wholesaleprice十进制(20，6)是0.000000

a_linkrewrite varchar(128)是NULL

a_metatitle varchar(128)是NULL

a_metakeywords varchar(255)是NULL

a_metadescription varchar(255)是NULL

a_ispublished tinyint(1) No 0

a_active tinyint(1)是1

a_createddate日期时间是NULL

a_createdby int(11)是NULL

a_modifieddate日期时间是NULL

a_modifiedby int(11)是NULL

Answer 1

尝试这个查询可能是优化的

SELECT 
    t_flowhistory.a_productid, 
    t_flowhistory.a_torole, 
    t_product.a_reference, 
    t_flowhistory.a_assigneddate 
FROM t_flowhistory 
join (SELECT max(a_flowhistoryid) as a_flowhistoryid FROM t_flowhistory 
         GROUP by a_productid) a on a.a_flowhistoryid=t_flowhistory.a_flowhistoryid     
INNER JOIN t_product ON t_product.a_productid = t_flowhistory.a_productid 
WHERE 
    (t_flowhistory.a_status like 'Assigned' or t_flowhistory.a_status like 'rejected') 
    and t_flowhistory.a_isresolved = '1' 
    and t_product.a_active = 0 
    and t_product.a_ispublished=0 
    and t_flowhistory.a_torole = 2 
ORDER BY t_flowhistory.a_assigneddate desc