How can I speed up the execution of this query (subselects)?

Question

This is my query, its running terribly slow. How can I improve the performance? Maybe using a join instead sunselects?

select 
sfoa.firstname, 
sfoa.lastname, 
sfo.increment_id 
from sales_flat_order_address sfoa 
join sales_flat_order sfo 
on sfoa.parent_id = sfo.entity_id 
where parent_id 
in 
(
SELECT
order_id
FROM 
(
SELECT
  sfoi.order_id,
  GROUP_CONCAT(sfoi.sku SEPARATOR ', ') AS skus
  FROM sales_flat_order_item sfoi
  GROUP BY sfoi.order_id) t
  WHERE t.skus LIKE '%whatever%'
  AND t.skus RLIKE '[0-9]'
);

Thanks for helping!

past here explain of this query... First step is to check indexe-es — Andrzej Reduta, Apr 15 '14 at 06:54
Already Explained Look Here. http://stackoverflow.com/questions/1244998/why-is-mysql-join-significantly-faster-than-where-in-subquery — Ragavendran Ramesh, Apr 15 '14 at 06:55

jmail · Answer 1 · 2014-04-15T07:03:38.493

0

Yes, IN with subqueries is slow. Use a join instead.

Subqueries execute every time you evaluate them (in MySQL anyway, not all RDBMSes), i.e. you're basically running 7 million queries! Using a JOIN, if possible, will reduce this to 1. Even if adding indexing improves performance of those, you're still running them.

https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

edited Apr 15 '14 at 07:03

answered Apr 15 '14 at 06:58

jmail

5,944
3
21
35

How can I speed up the execution of this query (subselects)?

1 Answers1