Cardinality counts distinct values in an index prefix; selectivity divides it by the rows. InnoDB estimates it from 20 random pages per index (innodb_stats_persistent_sample_pages) and refreshes it after 10% of rows change; ANALYZE TABLE refreshes it now. SHOW INDEX and information_schema.STATISTICS report it, and information_schema.TABLES the sizes.
ANALYZE TABLE big_orders;
SELECT index_name, column_name, cardinality FROM information_schema.STATISTICS
WHERE table_schema = 'shop' AND table_name = 'big_orders' AND seq_in_index = 1;
SELECT data_length DIV 1048576 AS data_mib, index_length DIV 1048576 AS index_mib
FROM information_schema.TABLES WHERE table_schema = 'shop' AND table_name = 'big_orders';
SELECT SUM(total) INTO @s FROM big_orders WHERE status = 'pending';
SELECT SUM(total) INTO @s FROM big_orders IGNORE INDEX (idx_status_date)
WHERE status = 'pending';
SELECT SUM(total) INTO @s FROM big_orders WHERE status = 'shipped';
SELECT SUM(total) INTO @s FROM big_orders IGNORE INDEX (idx_status_date)
WHERE status = 'shipped';Output
+--------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------+---------+----------+----------+ | shop.big_orders | analyze | status | OK | +--------------------+---------+----------+----------+ 1 row in set (0.036 sec) +----------------------+-------------+-------------+ | INDEX_NAME | COLUMN_NAME | CARDINALITY | +----------------------+-------------+-------------+ | idx_cust_status_date | customer_id | 50742 | | idx_status_date | status | 3 | | PRIMARY | id | 997984 | +----------------------+-------------+-------------+ 3 rows in set (0.001 sec) +----------+-----------+ | data_mib | index_mib | +----------+-----------+ | 37 | 39 | +----------+-----------+ 1 row in set (0.001 sec) Query OK, 1 row affected (0.039 sec) Query OK, 1 row affected (0.196 sec) Query OK, 1 row affected (1.147 sec) Query OK, 1 row affected (0.295 sec)
The sample missed a status (STATS_SAMPLE_PAGES enlarges it); two secondary indexes outweigh the data. Averages hide skew: for the 2% pending orders the index was five times faster than a scan, but for the 74% shipped ones the optimizer still chose it, at almost four times the cost. Keep such columns out of the lead, or use a histogram (Cost-Based Optimizer).