Bug #41133 InnoDB cardinality statistics are uneven

Description:
The InnoDB cardinality statistics have a strange pattern that probably indicates a random number generator isn't being truly random or something like that.

This pattern (3 of the values are shown about 2x as often as the other 5) is probably a symptom of a bug in InnoDB that should be fixed.

How to repeat:
Using the sakila sample database, run this for a while:

while true; do mysql sakila -N -e 'show index from film' | head -n 2 | tail -n 1 | awk '{print $7}'; done > sizes

CTRL-C when you think you have enough.  Then,

$ sort sizes | uniq -c
157 1022
156 1024
156 1058
156 1059
156 1131
313 951
312 952
312 953

Thank you for the report.

Verified as described.

reduced test case

Attachment: bug41133.test (application/octet-stream, text), 208.01 KiB.

Vasil is our optimizer statistics specialist.

would fix for bug #43660 (innodb_use_legacy_cardinality_algorithm=0) would fix this too ?

Shane,

Good catch!

This is indeed the same bug - the pattern (2 of 3) looks pretty much like "Bug #43660 SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table"

I confirm the problem goes away when innodb-use-legacy-cardinality-algorithm is set to 0:

(using Baron's steps to reproduce)

hag:/tmp/sakila-db$ wc -l sizes-*
    1099 sizes-innodb-use-legacy-cardinality-algorithm=1-default
    1072 sizes-innodb-use-legacy-cardinality-algorithm=0
    2171 total
hag:/tmp/sakila-db$ sort sizes-innodb-use-legacy-cardinality-algorithm\=1-default | uniq -c 
 100 1022
 100 1024
 100 1058
 100 1059
 100 1131
 200 951
 200 952
 199 953
hag:/tmp/sakila-db$ sort sizes-innodb-use-legacy-cardinality-algorithm\=0 | uniq -c   1 1017
   8 1018
  14 1019
  12 1021
  33 1022
  42 1024
  32 1025
  23 1026
  23 1028
   5 1029
   1 1030
   1 1032
   1 1051
   2 1052
   7 1054
  19 1055
  35 1057
  37 1058
  43 1059
  16 1061
  24 1062
  11 1063
   3 1065
   1 1069
   1 1123
   2 1124
   2 1125
  13 1127
  20 1128
  33 1129
  39 1131
  29 1132
  25 1134
  12 1135
   6 1136
   5 1138
   2 1139
   2 1140
   1 663
   1 665
   1 699
   1 709
   1 732
   1 733
   3 735
   2 736
   2 738
   6 739
   1 740
   1 742
   3 769
   2 771
   4 772
   5 773
   3 804
   2 805
   1 806
   8 808
   6 809
   6 810
   6 812
   2 813
   1 816
   1 817
   1 839
   4 841
  11 842
   7 843
   9 845
  12 846
   6 848
   1 849
   2 850
   3 875
   3 876
   5 878
   7 879
  12 881
   5 882
   1 883
   2 885
   3 908
   7 911
   5 912
  12 914
  16 915
  15 916
  16 918
  13 919
   3 920
   3 922
   1 923
   2 925
   3 945
   4 947
  13 948
  24 949
  21 951
  28 952
  29 953
  16 955
   6 956
   2 958
   2 959
   2 980
   2 981
   9 982
  17 984
  13 985
  16 986
   9 988
   9 989
   3 991
   1 993
hag:/tmp/sakila-db$ 

Thank you!

Setting as duplicate of Bug#43660 SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table