CODEWITHSUNDEEP
mysqlsqlperformanceoptimization
Shekhar Joshi
Shekhar Joshi

Reputation: 1008

Query optimization mysql for nested queries

I am not able to figure out how to optimize this query, please note that t_flowhistory table has ~16500 rows and the following query just does not execute, however the same query works just fine when fired on a smaller database. Is there any way I can optimize this query?

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

table structure for t_flowhistory:

Column Type Null default
a_flowhistoryid (Primary) bigint(20) No
a_productid bigint(20) Yes NULL
a_fromuserid int(10) Yes NULL
a_fromrole int(10) Yes NULL
a_torole int(10) Yes NULL
a_status enum('Assigned', 'Moved', 'Completed', 'Rejected') Yes Assigned
a_isresolved enum('0', '1') Yes 1
a_reasonid int(10) Yes NULL
a_remarks varchar(250) Yes NULL
a_assigneddate datetime Yes NULL

table structure for t_products

Column Type Null default
a_productid (Primary) int(11) No
a_reference varchar(42) Yes NULL
a_price decimal(20,6) Yes 0.000000
a_defaultcategoryid int(10) Yes 0
a_sequence int(10) Yes 100000
a_wholesaleprice decimal(20,6) Yes 0.000000
a_linkrewrite varchar(128) Yes NULL
a_metatitle varchar(128) Yes NULL
a_metakeywords varchar(255) Yes NULL
a_metadescription varchar(255) Yes NULL
a_ispublished tinyint(1) No 0
a_active tinyint(1) Yes 1
a_createddate datetime Yes NULL
a_createdby int(11) Yes NULL
a_modifieddate datetime Yes NULL
a_modifiedby int(11) Yes NULL

Upvotes: 0

Views: 499

Answers (1)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this query may be optimize 

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

Upvotes: 2

Related Questions