I have been trying to run an fairly large INSERT into an empty table joining two other tables now for several weeks and have not been able to get the query to run to completion even when sub-seting the data into smaller ranges.

I have tried this at MySQL releases 4.1.8a and 4.1.10a with no noticable improvement. The server is running Linux 2.4.21-4 Elsmp RedHat 3.2.3.-20. The server is dedicated to MySQL. The my.cnf file is attached. There are 8 Gbytes of RAM and 2, Hyperthreaded CPUs (top shows 4 processors). SHOW STATUS shows very good buffer hit ratio. Current stats are attached. Server was re-booted last Friday. It has been running the INSERT query since Saturday mid-morning and has yet to complete.

Table 1 230 million rows total compund PK index - 4 columns range 1 should select 35 million rows. Explain plan shows it to be using the PK Table 2 598 million rows compound PK index - 4 columns and one secondary index. Range 1 should select about 130 million rows

Table 3 Empty table unindexed. Two keys from Table 1 and 17 columns from table 2 populate this table

The general form of the query is:

INSERT INTO Table 3 (col1, .... Col18) SELECT Col1,,.col18 FROM Table 1 a INNER JOIN Table 2 b ON (PK columns and range selection) WHERE a.col5 = b.col5 AND .... AND ... AND a.col18 = b.col18

Any suggestions are welcome.