MySQL Forums
Forum List  »  Optimizer & Parser

Very slow dependent subquery
Posted by: patrick canarelli
Date: September 18, 2013 03:06AM

hi
I am querying the following and it is very very long (2 hours with customer < 300K records and btree indices and parameters setup correctly I believe) :

INSERT INTO temptable (CODE_CLIENT, TEMPVAR1)
SELECT
CODE_CLIENT ,
(SELECT SUM(t1.AmountPaid) FROM purchase AS t1 , member AS t2 WHERE t0.CODE_CLIENT = t2.CODE_CLIENT AND t2.EMAIL = t1.EmailAdress )
FROM customer AS t0


The explain of SELECT is :
id 1
select_type PRIMARY
table t0
type index
possible_key NULL
key IDX_CODE_CLIENT
key_len 63
ref NULL
row 282716
extra Using index

id 2
select_type DEPENDENT SUBQUERY
table t2
type ref
possible_key IDX_CODE_CLIENT, IDX_CODE_CLIENT_DATEJOIN, IDX_CODE_CLIENT_EMAIL, IDX_EMAIL, IDX_EMAIL_CODE_CLIENT
key IDX_CODE_CLIENT
key_len 63
ref test.t0.CODE_CLIENT
row 1
extra Using where

id 2
select_type DEPENDENT SUBQUERY
table t1
type ref
possible_key IDX_EmailAdress, IDX_EmailAdress_Date, IDX_Date_EmailAdress
key IDX_EmailAdress
key_len 768
ref test.t2.EMAIL
row 23
extra Using where


Tables are MYISAM
Parameters are :
key_buffer_size=256M
tmp_table_size=256M
max_heap_table_size=256M

This query is abnormaly slow even if purchase has 15M records as everything is indexed.
Would someone have any idea about what's wrong ?
Thanks for your help.
Patrick

Options: ReplyQuote


Subject
Views
Written By
Posted
Very slow dependent subquery
2654
September 18, 2013 03:06AM
1556
September 18, 2013 07:06AM
1504
September 18, 2013 07:51AM
1472
September 18, 2013 02:57PM
1395
September 18, 2013 02:13PM
1292
September 18, 2013 03:10PM
1332
September 24, 2013 10:04PM
1661
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.