Strange issue with mysql index cardiality I am facing a weird issue when trying to understand (explain) a query.

I have this table:

CREATE TABLE `trk_track` (
 `track_id` int(11) NOT NULL AUTO_INCREMENT,
 `track_date` date NOT NULL,
 `track_hour` int(11) NOT NULL,
 `track_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `user_id` int(11) NOT NULL,
 `action_id` int(11) NOT NULL,
 `url_utm_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`track_id`),
 KEY `track_date` (`track_date`,`track_hour`),
 KEY `user_id` (`user_id`),
 KEY `action_id` (`action_id`),
 KEY `url_utm_id` (`url_utm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=284538 DEFAULT CHARSET=utf8


There are about 200k lines in this table.

But something is very confusing:

1-) First the cardiality of url_utm_id index should be 200, but it shows '3' and after analyze table it shows '493', which is still wrong;

2-) When I try to run this query:

explain select count(track_id) from trk_track where url_utm_id in(185);


it shows:

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra   
1   SIMPLE  trk_track   NULL    ref     url_utm_id  url_utm_id  5   const   53  100.00  Using index


It is ok because there are only 53 url_utm_id(185) in trk_track.

But if I try to do:

explain select count(track_id) from trk_track where url_utm_id in(select url_utm_id from trk_url_utm where utm_id=102);


it shows:

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra   
1   SIMPLE  trk_url_utm     NULL    ref     PRIMARY,utm_id  utm_id  4   const   1   100.00  Using index
1   SIMPLE  trk_track   NULL    ref     url_utm_id  url_utm_id  5   db_trk.trk_url_utm.url_utm_id   577     100.00  Using index


This subquery:

select url_utm_id from trk_url_utm where utm_id=102


will return same '185' ID

But anyway it sounds like that it tries to make a join, look in explain and it scan 577 rows, it doesnt' make sense for me.

It should scan 53 also, shouldn't it?
1) cardiality in index of innodb table is  An estimate of the number of unique values in the index, MySQL Doc shows the details of it : https://dev.mysql.com/doc/refman/5.7/en/show-index.html

Change the innodb_stats_transient_sample_pages may get more accurate values, and https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html may help you.

2) rows in explain shows the the number of rows MySQL believes it must examine to execute the query, it's also a approximation. So the value only means MySQL believes is should examine 577 rows, not examine 577 rows exactly.