Cardinality is reported as "1" even though there are 2 unique values stored for the corresponding column

I am trying to optimize my database by adjusting indices.

SHOW INDEXES FROM my_table

outputs

Table     ...  Key_name    ...   Column_name  ...  Cardinality ...
---------------------------------------------------------------------
my_table  ...  idx_field1  ...    field1      ...      1       ...

while

SELECT field1 FROM my_table PROCEDURE ANALYSE()\G

outputs

*************************** 1. row ***************************
  Field_name: my_db.my_table.field1
  Min_value: ow
  Max_value: rt
  Min_length: 2
  Max_length: 2
  Empties_or_zeros: 0
  Nulls: 0
  Avg_value_or_avg_length: 2.0000
  Std: NULL
  Optimal_fieldtype: ENUM('ow','rt') NOT NULL
1 row in set (0.26 sec)

i.e., the reported cardinality (1) is not equal to the number of unique values (2). Why?

PS. I did perform

analyze table my_table

before running the queries.

Solution

The "cardinality" in SHOW INDEXES is an approximation. ANALYSE() gets the exact value because it is derived from an exhaustive scan of the table.

The former is used for deciding how to optimize a query. Generally, a low cardinality (whether 1 or 2) implies that an index on that field is not worth using.

Where are you headed with this question?