I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows.
The query includes 3 table
INNER JOINs, with each table having about 20,000,000 rows.
Further information, the process completes in about 12 seconds for a test file which has 1000 input rows, and returns 703 rows.
I started the query earlier ### we don't know when earlier is ###, but it is still running in the state: "Copying to temp table on disk" after 38000 seconds (10 and a half hours).
I think there is a problem during the insertion process. What am I probably doing wrong here? If it helps, the operating system of the computer is Windows 7, it has 3 GB RAM, an Intel Core2Duo 2.27GHz processor. ### you forgot to tell us details on the hard drive. One partition in, one out, same disk, same partitions, etc ###
Here's my query as it currently reads:
INSERT INTO kdd.contents (adid, descriptionwords, purchasedkeywordwords, titlewords) SELECT t.adid, dt.tokensid, pkt.tokensid, tt.tokensid FROM kdd.training t INNER JOIN kdd.purchasedkeywordid_tokensid pkt ON t.keywordid = pkt.purchasedkeywordid INNER JOIN kdd.titleid_tokensid tt ON t.titleid = tt.titleid INNER JOIN kdd.descriptionid_tokensid dt ON t.descriptionid = dt.descriptionid GROUP BY adid;