MySQL Bugs: #116440: The second scan of the index during a check table is not necessary.

Bug #116440	The second scan of the index during a check table is not necessary.
Submitted:	22 Oct 2024 5:01	Modified:	22 Oct 2024 10:32
Reporter:	Ke Yu (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
During the execution of the check table statement in innodb, the btree index is scanned twice.
The second scan is to compare the order between the records. The second scan is not necessary,
because during the first scan, the order of records has also been compared.

The second scan for primary index can be accelerated using parallel threads, this is also unnecessary in my opinion.

How to repeat:
From the code.

The following is the check table statement execution stack, the first time the index is scanned,
1.1 and 1.2 have finished comparing the order of all the records.

The second scan of the index, also to compare the order of records, is not necessary.

ha_innobase::check
|--> btr_validate_index // 1. the first scan
|    |--> btr_validate_level // from root level to leaf level
|    |    |--> loop：// traverse every level
|    |    |    |--> page_validate 
|    |    |    |    |--> for(;;) // traverse the records within the page
|    |    |    |    |    |--> cmp_rec_rec // 1.1 Compare the order between records within the page
|    |    |    |
|    |    |    |--> cmp_rec_rec  // 1.2 Compare the order of the supremum record of each page with the infimum record of its right node 
|
|--> row_scan_index_for_mysql // 2. the second scan，
|    |--> prev_entry->compare // traverse the leaf nodes and compare the order between records

Suggested fix:
Delete the second index scan to improve check table execution efficiency.

Hi Mr. Yu,

Thank you for your bug report.

We have consulted with our Development team in charge of this issue and we were told that second scan can not be just removed. First scan has to be changed in order to have a single scan.

Hence, it is concluded that what you propose is a good feature request.

This bug report is now verified as a feature request.

Hi,
  Can you tell me why the second scan index can not be deleted directly？
  
  Thank you!

Hi,

According to Development team in charge, the second scan does some additional checks. We have not received any further details except for the additional , necessary checks.

Development took a long look at it and decided that this is a very good feature request.

That can not be changed.