Re: Very slow dependent subquery

[Øystein Grøvlen]

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