I queried from a 20GB data table A, and the query result was 40,000 rows of data. My query was "select distinct custid from A where '2022-01-01' is between col1 and col2." Since col1 and col2 were indexed, I could obtain all 40,000 rows of data in just 3 seconds. However, when I wanted to insert this result into table B, the query became "insert into table B select distinct custid from A where '2022-01-01' is between col1 and col2," and it took 5 minutes. What is the reason for this?
5 minutes is the time required for a full table scan once.
The 'type' in the 'EXPLAIN' result for the SELECT statement changed from 'range' to 'ALL' when I added the 'insert' operation.
The MySQL optimizer believes that using an index during insertion may decrease efficiency, and thus, it defaults to a full table scan. However, when I use FORCE INDEX, the insertion speed significantly improves, and it no longer performs a full table scan. It is advisable to forcefully use an index at appropriate times instead of letting the optimizer decide the query plan.