MySQL 5.7.25 [innoDB], index cardinality suddenly reset/change back to 1

I am using MySql v 5.7.25 with innoDB.

I have a table inventories with 100 mio data

this is what it looks like when SHOW CREATE TABLE inventories;

note: removed unrelated field.

CREATE TABLE `inventories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `quantity` decimal(50,6) DEFAULT NULL,
  `line_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_inventories_on_line_number` (`line_number`),
  KEY `idx_pr_ln` (`product_id`,`line_number`),
) ENGINE=InnoDB AUTO_INCREMENT=39905 DEFAULT CHARSET=latin1

the problem is index cardinality on index idx_pr_ln suddenly reset to 1 on field line_number.

mysql > show index from inventories;

| Table       | Non_unique | Key_name                              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|-------------|------------|---------------------------------------|--------------|-------------|-----------|-------------|----------|--------|------|------------|---------|---------------|
| inventories |          1 | index_inventories_on_line_number      |            1 | line_number | A         |     3025563 |          |        | YES  | BTREE      |         |               |
| inventories |          1 | idx_pr_ln                             |            1 | product_id  | A         |     4337902 |          |        | YES  | BTREE      |         |               |
| inventories |          1 | idx_pr_ln                             |            2 | line_number | A         |           1 |          |        | YES  | BTREE      |         |               |

every SELECT query that use index idx_pr_ln now do FULL table scan because of cardinality 1 on line_number.

after I do ANALYZE TABLE inventories, it will back to the 'correct' value around 100 mio. but in a few day, it happened again (reset to 1 again).

my question are

why this cardinality suddenly reset/change to 1?

have any of you guys ever experience this?

is this a bug on MySql?

Any explanation / suggestion are appreciated. Thanks.

NOTE: no changes on table or index, no alter table, no add / remove index. only data that may changes (CUD)

EDIT: the select statement is like this

SELECT product_id, line_number FROM inventories WHERE product_id = 123 AND line_number < 321

Solution

I found the problem for my question, that cause index cardinality to be 1.

as @Rick James mentioned

InnoDB will make a few random probes to determine statistics (cardinality) of columns. If it happens to see unique values for line_number, then the cardinality is set to 1. If not, some larger value is used.

and after reading this mysql doc https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

this was cause by statistic update by mysql when there is 10% of the data (around 10 mio in this case) has changed.

STATS_AUTO_RECALC specifies whether to automatically recalculate persistent statistics. The value DEFAULT causes the persistent statistics setting for the table to be determined by the innodb_stats_auto_recalc setting. A value of 1 causes statistics to be recalculated when 10% of table data has changed. A value 0 prevents automatic recalculation for the table. When using a value of 0, use ANALYZE TABLE to recalculate statistics after making substantial changes to the table.

STATS_SAMPLE_PAGES specifies the number of index pages to sample when cardinality and other statistics are calculated for an indexed column, by an ANALYZE TABLE operation

with the default setting STATS_AUTO_RECALC=1 and STATS_SAMPLE_PAGES=20, there is a chance that mysql scan sample pages that contain a distinct value (only 1 distinct value on 20 pages sample).

after I update STATS_SAMPLE_PAGES=30, it less likely to have cardinal 1.