Query optimization mysql for nested queries

Question

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

Table definitions for the 2 tables would be helpful

Mihai
– Mihai

2015-07-07 04:55:40 +00:00
Commented Jul 7, 2015 at 4:55 — Mihai
– Mihai, Commented Jul 7, 2015 at 4:55

Mukesh Kalgude · Accepted Answer · 2015-07-07 05:22:00Z

2

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

edited Jul 7, 2015 at 5:22

answered Jul 7, 2015 at 4:57

Mukesh Kalgude

4,8542 gold badges21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Shekhar Joshi Over a year ago

You removed a whole subquery dude, I need the columns returned by that subquery.

Mukesh Kalgude Over a year ago

From t_flowhistory as table to return all columns. i Remove subquery to specify 'in'. And add extra join look that query

Shekhar Joshi Over a year ago

I get this error: #1054 - Unknown column 'a.a_flowhistoryid' in 'on clause'

Mukesh Kalgude Over a year ago

Ok add alias name to this query check it now

Shekhar Joshi Over a year ago

yeah it's running, but seems to take too long...loading.

|

Collectives™ on Stack Overflow

Query optimization mysql for nested queries

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related