Bug #117173 InnoDB index stats many orders of magnitude wrong

• Submitted: 10 Jan 1:43
• Modified: 10 Jan 11:18
• Reporter: Matheus Aires
• Status: Can't repeat
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.39
• OS: Any
• CPU Architecture: Any
• Tags: Index stats, innodb

[10 Jan 1:43] Matheus Aires

Description:
When analyzing a table with a low cardinality index, if number of sample pages is lower than the number of pages on level (root - 1) it will not scan this level, it will chose root and because of the formula used for calculation of index this index will be many orders of magnitude wrong. It could cause bad plan to be used

How to repeat:
Create a table with a secondary index on a low cardinality column (around 20). Insert records until the B+tree has 3 level and pages on level 1 be greater than sample_pages. analyze table.

Suggested fix:
The algorithm should be able to know it is running on a bad case for the standard expression and have a fallback scenario that would avoid a massive error. Improvement of algorithm

[10 Jan 11:18] MySQL Verification Team

Hi Mr. Aires,

Thank you very much for your bug report.

We have tried to repeat what you reported, but we failed to repeat the behaviour that you describe.

Let us also inform you about the purpose of this forum. This forum is dedicated ONLY to the reports with fully repeatable test cases. Each of this test cases should consist of the series of SQL statements that always result in the problem that is reported.

We can not repeat your report without test case as described above.

Can't repeat.