Performance testing in MYSQL giving same result

Question

I am developer and working on performance improvment on MySQL queries. The flow is full load in 2 stages. First query will read FILES table and load into TABLE_STAGE table with full delete and in 2nd level also it read from TABLE_STAGE to TABLE_MAIN table. First delete it and then select all the records.

delete from TABLE_STAGE
select from FILES 
delete from TABLE_MAIN ;
insert into TABLE_MAIN from select * from TABLE_STAGE

As a first part of step i have replaced delete with truncate . It improved performance immediately , but when i using delete again , performance is same ,the time is not increasing. I am not getting the reason behind this why it is showing the same result..

question from:https://stackoverflow.com/questions/65845230/performance-testing-in-mysql-giving-same-result

Answer 1

Don't use DELETE, it is slow.

CREATE TABLE new LIKE real;
LOAD DATA INFILE INTO new (or use batched insert)
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

(I don't understand where your FILES table is used.)