Bug #108707 Clarify manual on Estimating Query Performance
Submitted: 7 Oct 2022 11:47 Modified: 7 Oct 2022 13:10
Reporter: Bill Karwin (Candidate Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[7 Oct 2022 11:47] Bill Karwin
The manual page on "Estimating Query Performance" is unclear and I believe is now outdated. It seems to assume the user uses MyISAM, though it doesn't mention MyISAM.


It makes reference to a formula for calculating the number of disk seeks, based in part on the index block size and data pointer size.

These factors are not explained or given a link. 

I guess that index block size refers to MyISAM key cache block size (https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_key_cache_block_si...), but since the name is not exactly the same, it's not clear.

I guess that the data pointer size refers to the myisam_data_pointer_size (https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_myisam_data_pointe...), which is specific to MyISAM.

The focus on disk seeks to calculate query performance seems like it's advice from years ago, prior to the widespread adoption of solid-state storage. Granted not every site uses SSD, but it's now popular enough that it should at least be presented as a mainstream choice.

The method of estimating query performance on this manual page is irrelevant to InnoDB, since InnoDB doesn't have a key cache, it has a buffer pool.

How to repeat:
Read the manual page mentioned above and try to apply it to a modern MySQL instance. Become confused. 

Suggested fix:
Clarify that disk seeks are much less of a factor on solid-state storage.

Clarify that the formula given on this manual page is specific to MyISAM.

Research with InnoDB R&D to produce an equivalent formula for InnoDB, and present that as the preferred measure to estimate query performance, given that InnoDB is the default storage engine.
[7 Oct 2022 13:10] MySQL Verification Team
Hi Mr. Karwin,

Thank you for your bug report.

We agree with you ......

This report is now a verified bug.