When we wanted to quickly process intermediate data by using temporary or heap tables; then its normal tendency to create a table like its source table; so people will simply opt for CREATE TEMPORARY TABLE temp_table LIKE source_table.
This is an easy and convenient way. But the problem is if you have a simple stored procedure or code module that gets executed frequently, then you might indirectly experience a slow-down in the performance if your source_table is large enough and highly contended. Things will be really worst if concurrent threads starts using the source_table or in combination of source_table and CREATE LIKE.. statements and can lead to disaster scalability issues. The main reason for the post is; I noticed from SHOW PROCESSLIST from random servers where X threads getting into WAITING for TABLE lock state for simple …
[Read more]