mysql optimization - Questions about index failure and the number of scanned rows being greater than the total number of data rows during mysql query.

Question

The data table uses the innodb engine, and the updated_at field uses a normal index.

Three questions:

1. Comparing the first two statements, the second statement does not use an index. I remember that the index will be given up when the number of scanned rows reaches a certain number. What is the critical value?

2. Full table scan shows that the number of scanned rows is 70341, but the total number of data rows is only 57087?

3. select count(*) uses an index, but also scans 70341 rows. Will this statement cause performance problems?

Answer 1

1. In the database of the CBO optimization mechanism, there is no clear critical value for using or not applying indexes. The minimum COST in the execution plan is used as the standard. The empirical value is that it is more appropriate to use indexes when the total number of rows in the table is less than 5%.

2. I understand that the second statement uses the statistical data of the table. If the table has recently undergone major changes and the statistical data has been updated in time, there will be a large deviation between the two.

3. count(*) uses an index, which means that the update_at field has the definition of NOT NULL. Compared with a full table scan, the cost of scanning the index will be lower.