MySQL Forums
Forum List  »  Optimizer & Parser

Re: Very slow dependent subquery
Posted by: Øystein Grøvlen
Date: September 18, 2013 07:06AM

Hi,

I think the following query should be equivalent and perform better, at least in MySQL 5.6:

INSERT INTO temptable (CODE_CLIENT, TEMPVAR1)
SELECT t0.CODE_CLIENT, td.sum FROM customer AS t0 LEFT JOIN
(SELECT t2.CODE_CLIENT, SUM(t1.AmountPaid) AS sum FROM purchase AS t1, member AS t2 WHERE t2.EMAIL = t1.EmailAdress ) AS td ON t0.CODE_CLIENT = td.CODE_CLIENT;

If there is a match in member for every row in customer, or you do not care about rows without a match, you can replace LEFT JOIN with INNER JOIN and it should perform better even in older versions of MySQL.

Hope this helps,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote


Subject
Views
Written By
Posted
2437
September 18, 2013 03:06AM
Re: Very slow dependent subquery
1469
September 18, 2013 07:06AM
1401
September 18, 2013 07:51AM
1366
September 18, 2013 02:57PM
1300
September 18, 2013 02:13PM
1208
September 18, 2013 03:10PM
1239
September 24, 2013 10:04PM
1550
September 25, 2013 12:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.