MySQL Forums
Forum List  »  Optimizer & Parser

Why MySQL adding 1 when calculating the records per key?
Posted by: ffc ddd
Date: September 24, 2022 07:16PM

When calculating the records per key, we see the MySQL using the "rec_per_key_t(tab->records()) / distinct_keys_est + 1;" ,my question is why it doesn't use the "rec_per_key_t(tab->records()) / distinct_keys_est ;" instead ,why it needs the "+ 1" here?

The code segment is below:

Assume that the first key part matches 1% of the file
and that the whole key matches 10 (duplicates) or 1
(unique) records.
Assume also that more key matches proportionally more
This gives the formula:
records = (x * (b-a) + a*c-b)/(c-1)

b = records matched by whole key
a = records matched by first key part (1% of all records?)
c = number of key parts in key
x = used key parts (1 <= x <= c)
rec_per_key_t rec_per_key;
if (keyinfo->has_records_per_key(
keyinfo->user_defined_key_parts - 1))
keyinfo->records_per_key(keyinfo->user_defined_key_parts - 1);
rec_per_key_t(tab->records()) / distinct_keys_est + 1;

Options: ReplyQuote

Written By
Why MySQL adding 1 when calculating the records per key?
September 24, 2022 07:16PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.