Large records table insertion issue Mysql

I am a developer and I am facing an issue while managing table which has large amount of records.

I am executing a cron job to fill up data in primary table (Table A) which has 5-6 columns and approx 4,00,000 to 5,00,000 rows and then creating another table and data in this table would continue to increase over the time.

TABLE A contains the raw data and my output table is TABLE B

My cron script truncates data in Table B then inserts data using select query

TRUNCATE TABLE_B; 

INSERT INTO TABLE_B (field1, field2) 
SELECT DISTINCT(t1.field2), t2.field2 
FROM TABLE_A AS t1 
INNER JOIN TABLE_A t2 ON t2.field1=t1.field1 
WHERE t1.field2 <> t2.field2 
GROUP BY t1.field2, t2.field2 
ORDER BY COUNT(t1.field2) DESC; 

Above select query produces approx 1,50,000 to 2,00,000 rows

Now it takes too much time to populate TABLE B and meanwhile If my application tries to access TABLE B then select query fails

Explaining query results following:

'1','PRIMARY','T1','ALL','field1_index',NULL,NULL,NULL,'431743','Using temporary;Using filesort' 
'1'开发者_开发百科,'PRIMARY','T2','ref','field1_index','field1_index','767','DBNAME.T1.field1','1','Using where' 

Can someone please help me in improving this process, or guide me alternatives for above process?

Thanks

Suketu

---

You should do the whole process in a stored proc.

Do not truncate such a large table. Follow the following steps:

1. Copy the TableB structure to TableB_Copy.
2. DROP TABLEB.
3. Rename TableB_Copy to TableB
4. Disable indexes on TableB
5. Insert the data from TableA into TableB
6. Create the indexes on TableB.

---

According to my view the solution would be like this:

SELECT 
    DISTINCT(t1.field2), t2.field2 
    FROM 
        TABLE_A AS t1
    INNER JOIN 
        TABLE_A t2 ON 
        t2.field1=t1.field1 
    WHERE
        t1.field2 <> t2.field2 
    GROUP BY
        t1.field2, t2.field2 
    ORDER BY
       COUNT(t1.field2)
    DESC INTO OUTPUT "PATH-TO-FILE";

For instance file as "C:\TEMP\DATA1.SQL". What will happen with this query a simple new file is created with TAB delimiter to insert into any table. Now how to import the data to table.

LOAD DATA
    "PATH-TO-FILE"
INTO TABLE
     table_name

With this query the data will be inserted and on the other hand you will be able to use the table in which you are inserting the data.