MySQL Forums
Forum List  »  Optimizer & Parser

Re: Very slow dependent subquery
Posted by: Rick James
Date: September 24, 2013 10:04PM

key_buffer_size=256M
tmp_table_size=256M
max_heap_table_size=256M
I checked that about 1 Go of RAM is available

What do you mean by "is available"? If you have only 1GB of RAM, then those settings are MUCH TOO HIGH. 20M might be safer for each.
If you really have more than 1GB (total) of RAM please state how much.

> as everything is indexed.
Please provide SHOW CREATE TABLE.
If you have an index on each column, that is an unnecessary burden.

> key_len 63
> key_len 768
Are the keys wider than they need to be?

> possible_key IDX_EmailAdress, IDX_EmailAdress_Date, IDX_Date_EmailAdress
Smells like redundancy. If you have
INDEX(EmailAddress), INDEX(EmailAddress, Date), then the former is redundant and can be DROPped.

> purchase has 15M records
Does the query need to touch all the rows? If so, that is a lot of disk I/O; hence the query is destined to take a long time. However, we can work on improving it.

Does this generate the right data? (for the first few values)SELECT m.CODE_CLIENT,
SELECT  m.CODE_CLIENT, SUM(p.AmountPaid)
    FROM  purchase AS p
    JOIN  member AS m ON m.EMAIL = p.EmailAdress
    GROUP BY  m.CODE_CLIENT
    LIMIT  11;
And provide the EXPLAIN of it.

If so, then how fast does this run?
INSERT  INTO temptable (CODE_CLIENT, TEMPVAR1) 
SELECT  m.CODE_CLIENT, SUM(p.AmountPaid)
    FROM  purchase AS p
    JOIN  member AS m ON m.EMAIL = p.EmailAdress
    GROUP BY  m.CODE_CLIENT;

And if you are still not happy, then consider creating and maintaining a "summary table". See
http://mysql.rjweb.org/doc.php/datawarehouse
It may be possible to transform the process into something that is 10 times as fast. (No, the INSERT..SELECT will not survive as is.)

Options: ReplyQuote


Subject
Views
Written By
Posted
2437
September 18, 2013 03:06AM
1470
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
Re: Very slow dependent subquery
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.