Cardinality discrepancy I have two, almost identical, databases and I am trying to figure out what is causing queries to perform that much slower in one of them. While investigating using the information_schema.statistics table I noticed that the cardinality column for one of the indexed fields was triple in size compared to the same column in the other database. e.g.
For the first database the cardinality is

+------------------------+-------------+
| column_name            | cardinality |
+------------------------+-------------+
| date_time              |      46202 |
+------------------------+-------------+


and for the second database:

+------------------------+-------------+
| column_name            | cardinality |
+------------------------+-------------+
| date_time              |      185052 |
+------------------------+-------------+


Since the cardinality is


  An estimate of the number of unique values in the index.


I got wondering about the unique date_time  for this columns. Turns out that they are the same (select distinct date_time from table = 54187).

So if the unique values are exactly the same, how come the cardinality in the second database for the column date_time is almost four times the one on the first database?
The cardinalities are estimates.
They have limited use, so don't worry if the values are as far off as you see.


If you are worried that some queries are being Optimized inadequately, let's see the queries, SHOW CREATE TABLE, and EXPLAIN SELECT ...  There are many things that can be looked at; most do not involve cardinality.

A typical solution involves a "composite" index.  For example:

WHERE a=1 AND b=2


If you have INDEX(a), INDEX(b), the Optimizer will use only one of the indexes and may use cardinality to decide between them.

If you have INDEX(a,b), the decision is obvious, and cardinality is irrelevant, and the query will run faster.