Why does Cardinality become 1 when I show index?

Question

Initially, the database monitoring platform had slow sql alerts.

SELECT * FROM t1 WHERE deduct_status = 1 AND bill_date = '2023-02-15' AND type IN (1, 2) AND gs_id IN (794872, 794873, 794874, 794875, 532720, 794868, 794869, 794870, 794871, 794864);

I first queried the information of this table. The table structure is as follows. I omitted some irrelevant sensitive fields and comments.

CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`gs_id` int(10) NOT NULL,
`bill_date` date NOT NULL ,
`account_user_id` bigint(20) NOT NULL DEFAULT '0',
`type` tinyint(2) NOT NULL DEFAULT '-1' ,
`bill_count` int(10) NOT NULL DEFAULT '0' ,
`deduct_status` tinyint(2) NOT NULL DEFAULT '0',
`deduct_amount` decimal(10, 2) NOT NULL DEFAULT '0.00',
`version` int(10) NOT NULL DEFAULT '0',
`gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`creator` varchar(50) NOT NULL DEFAULT '' ,
`gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_gsid_billdate_type_accountid` (`gs_id`, `bill_date`, `type`, `account_user_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 377270771 DEFAULT CHARSET = utf8mb4

As normal, this sql would query through the unique index uk_gsid_billdate_type_accountid.
So, I think maybe there is something wrong with the index. cause mysql's optimizer to choose not to query by index.

Then I executed the following sql.

show INDEX from t1; 

The execution results are as follows,I hide the table name：

Here is some other information I think useful:

SELECT index_name, stat_name, stat_value, stat_description
       FROM mysql.innodb_index_stats WHERE table_name like 't1';

show variables like 'innodb_stats%';

select  version();

I want to know why CARDINALITY is 1 and if this is the reason for slow sql.

After a night,I re-excuted this sql,This table is back to normal.I didn't do anything.

show INDEX from t1; 

Answer 1

That index starts with what is probably a low-cardinality gs_id. Furthermore, that column is not tested with =, so the Optimizer would get past gs_id.

WHERE deduct_status = 1
  AND bill_date = '2023-02-15'
  AND type IN (1, 2)
  AND gs_id IN (794872, 794873, ...)

The optimal index be something like:

INDEX(deduct_status, bill_date,   -- either order is OK
      type, gs_id)                -- again, either order

Even so, the cardinality of the first 2 columns may not be sufficient to warrant using the index.

Run ANALYZE TABLE t1;; it may update the cardinalities.