I know this question has been asked before but I could not fix my problem yet so far after finding answer here and there. I am really frustrated so I tried to ask again here.
I have a table with 76 million rows (newpaper_type1_ACD) and I wanna join them with another table with around 7 million rows (bigdatasetauthorseparated).
The query is as follows:
insert into newpaper_type1_ADE
(paperkeyA, paperkeyD, authorE)
select a.paperkeyA, a.paperkeyD, b.author
from newpaper_type1_ACD_notempty a
left join bigdatasetauthorseparated b
on a.paperkeyD = b.paperkey
order by b.paperkey, b.author;
The paperkey is indexed already.
I have tried to execute same query before but in smaller data and it worked. But now it always stopped after 3800 second.
I tried to change this also but it did not work:
set global tmp_table_size = 134217728;
set global max_heap_table_size = 134217728;
And I noticed that mysql uses the temporary table by default in C:/windows/temp. I tried to change the path to TEMP and TMP in environmental variable setting into another hard drive but it also did not work.
My C drive has 201GB of free space. and my E and F drive has around 9TB of free space. My OS is Windows Server 2012.
Some article said I should change my.cnf file and change the tmpdir location but I could not find this file. I have my.ini file but i did not see any tmpdir variable inside.
Any help would be really appreciated. Thank you.
EDIT:
Even if I have changed the Windows Environment Variable Setting into another hard drive (F:/temp), when I check in the MySQL Command Prompt using:
show variables like 'tmpdir';
the tmpdir is still assigned to C:/Windows/Temp. Can anybody tell me how to change the tmpdir inside the MySQL?
