How to analyze the slow execution of an insert statement on a specific table?

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 某张表的insert 语句执行很慢,应该怎么分析?

| username: TiDBer_QHSxuEa1

Information of Table A:
Data volume: 80,000
Table structure: Mainly consists of common field types such as varchar, char, and int, with a total of 24 fields.
Including the auto-increment primary key, there are a total of 4 indexes:
primary, unique(column1, column2), idx1(column1, column3, column4), idx2(column3, column4);

Because I didn’t want to modify the original table, I copied a temporary table A_temp;
When performing insert into A_temp select * from A, it took 5 minutes for 80,000 records.

Then I tried inserting into other tables:
insert into B_temp select * from B, 74,000 records took only 5 seconds; the difference is too large.

The information of Table B is similar to Table A, including the auto-increment primary key and 3 indexes:
primary, unique(column1, column2, column3, column4), idx1(column5, column2);

From which aspects and methods can I analyze the reasons for this situation?

| username: zhanggame1 | Original post link

First, check the actual number and size of regions for tables A and B. TiDB’s full table scan is related to the number of regions.
show table A regions

| username: TiDBer_小阿飞 | Original post link

EXPLAIN ANALYZE select * from A
EXPLAIN ANALYZE select * from B
Compare them

| username: 小龙虾爱大龙虾 | Original post link

First, find these two executions from the slow log and post the execution plans for us to take a look.

| username: 像风一样的男子 | Original post link

If you have the patience, you can check this link and troubleshoot:

| username: forever | Original post link

Are the table types the same, both clustered or non-clustered tables?

| username: andone | Original post link

Is it possible for blocking and deadlock issues to exist?

| username: 随缘天空 | Original post link

It is possible that the table’s statistics are inaccurate. You can refer to the image below for a quick search, and then try executing the command ANALYZE TABLE t1;

| username: Kongdom | Original post link

Are you referring to the distinctiveness of the index field values in Table A? If they are all GUID-type fields, it might take relatively longer, right?

| username: dba远航 | Original post link

In the absence of other transaction impacts, it also depends on the number of fields in the two tables, including field types. If there are LOB fields, it will be much slower.

| username: tidb菜鸟一只 | Original post link

I think you can check the hotspots to see if they are being written again.

| username: 心在飞翔 | Original post link

The probability of locking is relatively high. You can create a temporary table B_temp and copy the data from A_temp to B_temp in the same way to see if the copy time is normal.