Could somebody help me with the AvgRowsPerBlock column returned in SHOW STATISTICS.
I have a table with large columns (each about 64KB) and a datablock size of 256KB. On average i would expect 4 rows per data block. BUt if i run SHOW STATISTICS, the returned value is 337.50. Does anybody know why this number is much higher?
Thanks in advance
COLLECT SUMMARY STATISTICS
/* Version */ 6,
/* AvgRowsPerBlock */ 337.500000,
/* AvgBlockSize (bytes) */ 256512.000000,
/* AvgRowSize */ 64033.000000,
I noticed exactly the same:
AvgRowSize and AvgBlockSize are both correct (vertified by Ferret and COLLECT DEMOGRAPHICS) and AvgRowsPerBlock returns values much higher or lower than expected, including impossible values like 80000.
I never care about it :-)
Optimizer team has opened a bug report to fix the calculation. Good news is that Dieter's solution to ignore it is fine since that value is not used in query optimization from that source.
I wonder if this information is available somewhere else? I am asking because as far as I know is the usage of secondary indexes dependend from the average rows per data block(in order to determine selectivity)?
I was hoping that this information shown in the SHOW STATISTICS would be used by the Optimizer...and i could use it to estimate index usage.