• Monty's avatar
    MDEV-34664: Add an option to fix InnoDB's doubling of secondary index cardinalities · 4bf7c966
    Monty authored
    (With trivial fixes by sergey@mariadb.com)
    Added option fix_innodb_cardinality to optimizer_adjust_secondary_key_costs
    
    Using fix_innodb_cardinality disables the 'divide by 2' of rec_per_key_int
    in InnoDB that in effect doubles the Cardinality for secondary keys.
    This has the biggest effect for indexes where a few rows has the same key
    value. Using this may also cause table scans for very small tables (which
    in some cases may be better than an index scan).
    
    The user visible effect is that 'SHOW INDEX FROM table_name' will for
    InnoDB show the true Cardinality (and not 2x the real value). It will
    also allow the optimizer to chose a better index in some cases as the
    division by 2 could have a bad effect for tables with 2-5 identical values
    per key.
    
    A few notes about using fix_innodb_cardinality:
    - It has direct affect for SHOW INDEX FROM table_name. SHOW INDEX
      will also update the statistics in table share.
    - The effect of fix_innodb_cardinality for query plans or EXPLAIN
      is only visible after first open of the table. This is why one must
      do a flush tables or use SHOW INDEX for the option to take effect.
    - Using fix_innodb_cardinality can thus affect all user in their query
      plans if they are using the same tables.
    
    Because of this, it is strongly recommended that one uses
    optimizer_adjust_secondary_key_costs=fix_innodb_cardinality mainly
    in configuration files to not cause issues for other users.
    4bf7c966
secondary_key_costs.result 6.79 KB