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.