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?