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.)