Bug #80128 reserved pages doesn't fit the context of index stats
Submitted: 23 Jan 2016 23:08 Modified: 15 May 2018 12:48
Reporter: Trey Raymond Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[23 Jan 2016 23:08] Trey Raymond
Index statistics currently uses the total index size in pages, which includes all allocated pages, many of which are empty (very low fill rates are common, I see things in the 60-70% range all the time).  while finding the total size of an index in a tablespace is useful and necessary functionality, in the case of index stats it's not the appropriate metric.  it should be using the number of used pages, which is useful for estimating the cost of a lookup in pages read (index height), etc - things relevant to the use case of index stats.

The code is already calculating this value:
but the current method chosen, checking total size, just throws that value away and uses the returned value, which is total reserved pages:

How to repeat:
look at your indexes!  this applies globally.  if you want to get a quick shock, make a table with a single large primary key (around the max of 3K), and insert a few hundred rows.  you'd expect it to be bad, as a key that large will have a low max branching factor, but if you take a look, index stats will claim it has more internal nodes than leaves.  in reality, of course, this is not the case - it will have a bunch of allocated empty pages.

Suggested fix:
we don't want to change functionality for anything else looking at btree data, so we want to leave existing calls in place, but introduce new elseif condition for a new option, constant named what you want, like "BTR_USED_SIZE"

change the arg here from BTR_TOTAL_SIZE to a newly introduced value for BTR_USED_SIZE
you should change line 840 as well for non-persistent stats, to maintain similar functionality, consistency is good
[15 May 2018 12:48] MySQL Verification Team

Thank you for your report.

I find it to be a reasonable feature request.

Verified as a feature request.