Description:
In a previous bug report (https://bugs.mysql.com/bug.php?id=118054), I reported a potential optimization in row_ins_sec_index_entry_low() that can eliminate one B-tree search in specific conditions. It has been verified.
Here, I want to propose another optimization that is more general and can further reduce the number of B-tree searches.
------
Analysis
When inserting a record into a unique key index, the current steps are:
1. Search the tuple (with n_fields_cmp set to the full number of columns) in the B-tree.
2. If a record is found that’s duplicated on index columns, then:
3. Search the tuple (with n_fields_cmp set to the unique columns only) in the B-tree.
4. Starting from the first record, continue moving to the next and check if a truly duplicated record exists. If not, then:
5. Search the tuple again (with n_fields_cmp set to the full number of columns) in the B-tree to find the exact insert point.
6. Insert the new record at that position.
I explained why there are 3 B-tree searches here in the previous report.
The current implementation always sets n_fields_cmp = all columns for the secondary index, regardless of whether the index is unique or not.
------
Proposed Optimization
For a unique index, we could change the approach:
1. Search the tuple (with n_fields_cmp set to the unique columns only) in the B-tree.
1.1 If the record it stops at is not duplicated with the tuple, then insert directly.
1.2 If the record is duplicated with the tuple, continue to move to the next and check if a true duplicate exists. At the same time, decide the proper insert point for the entry (using all columns here).
1.3 If no real duplicate is found, insert the entry directly at the determined insert point.
------
Benefits
This approach requires only one B-tree search.
It can significantly reduce page latch conflicts and improve concurrency. Even in the worst case, the number of record comparisons wouldn’t exceed the current implementation.
How to repeat:
It can be easily reproduced by inserting into a unique index that contains many potential duplicate records for the inserted tuple.
You can use the same reproduction approach as in my previous report: https://bugs.mysql.com/bug.php?id=118054.
