My DB schema of MySql 8.0.19 has a table with indexes on 3 columns of it. Table has around 40,000 rows. Distinct value of those 3 columns are 19k, 5k, 10. Primary key is auto generated id. I was expecting cardinality value of information_schema.statistics for this table to be around these values. But to my wonder, it is set to 0 for all 4 columns.
I debugged to this point as query involving join to this table is taking a lot of time. I strongly doubt that the index is either corrupt or not getting built. It is innodb table.
I have already tried deleting all the data from the table and reinserting but still query is taking time and cardinality is 0.
As compared to other tables, check table table_name command also takes significantly longer for this table.
Can someone give some pointer why indexes are not getting built and cardinality remains 0? Thanks in advance.