Search code examples
mysqlindexingcardinality

How to understand the cardinality in a combined primary in MySQL

I am running the command:

show index from atpco_fare.atpco_r1_fare_cls ;

And the result is in the table 

Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment, Visible 
atpco_r1_fare_cls   0   PRIMARY 1   tar_nbr A   7707    BTREE   YES 
atpco_r1_fare_cls   0   PRIMARY 2   carr_cd A   46531   BTREE   YES 
atpco_r1_fare_cls   0   PRIMARY 3   rule_nbr    A   426326  BTREE   YES 
atpco_r1_fare_cls   0   PRIMARY 4   fare_cls    A   30087162    BTREE   YES 
atpco_r1_fare_cls   0   PRIMARY 5   seq_nbr A   21610918    BTREE   YES 
atpco_r1_fare_cls   0   PRIMARY 6   mcn A   31260526    BTREE   YES 
atpco_r1_fare_cls   0   PRIMARY 7   eff_date    A   31260526    BTREE   YES 
atpco_r1_fare_cls   0   PRIMARY 8   proc_ind    A   31260526    BTREE   YES

I do not understand the cardinality of the combined primary key as it does not show fully that with the increase of sequence it should be more unique rather than less. At least, it should not be less, based on my understanding of index.

First question is why is the seq_nbr which is the sequence number has less cardinality. What does it indicate?

Second, why are the last three with the same cardinality? What does it indicate?

Solution
    • Cardinality is an approximation.
    • Cardinality in a multi-column index has very little importance.
    • Is some subset of those 8 columns 'unique'? If so, you should shorten the PK.
    • What are the datatypes of the columns? (Please provide SHOW CREATE TABLE.)
    • Are there about 31M rows in the table? If so, then the estimates indicated that the index was already 'unique'. (This guess could be wrong.)
    • What version of MySQL? (The probing to get statistics has changed in the last couple of major versions.)
    • Please explain what you expect to do with the cardinalities.