CODEWITHSUNDEEP
mysqlquery-optimizationsubquery
user1540714
user1540714

Reputation: 2089

MySql and subselect, why is it so slow?

I have this select here:

select parent_id from sales_flat_order_status_history where status like '%whatever%' group by parent_id having count(parent_id) > 1

This query runs only some seconds. Now I want to use it in another select, like this:

select increment_id from sales_flat_order where entity_id in(
select parent_id from sales_flat_order_status_history where status like '%whatever%' group by parent_id having count(parent_id) > 1)

This runs forever, so I tried inserting the ids one by one:

select increment_id from sales_flat_order where entity_id in(329,523,756,761,763,984,1126,1400,1472,2593,3175,3594,3937,...)

This runs fast, where is the difference and how can I make my first approach work faster?

Thanks!

Upvotes: 2

Views: 185

Answers (2)

Michael Ishmael
Michael Ishmael

Reputation: 56

Your query is taking a long time to run because it's executing the subquery and doing a lookup for every row sales_flat_order table.

A join will probably be quicker:

select increment_id 
from sales_flat_order
  inner join (select parent_id 
              from sales_flat_order_status_history 
              where status like '%whatever%' 
              group by parent_id having count(parent_id) > 1) Sub 
  on sales_flat_order.entity_id  = Sub.parent_ID

This forces the subquery to execute only once

Upvotes: 3

edze
edze

Reputation: 3015

Subqueries are processed in foreach style (for each row do subselect).

Thinks like in (1,2,3) don't use an index with some older versions of mysql.

Upvotes: 1

Related Questions