SHOW STATS_HEALTHY

The SHOW STATS_HEALTHY statement shows an estimation of how accurate statistics are believed to be. Tables with a low percentage health may generate sub-optimal query execution plans.

The health of a table can be improved by running the ANALYZE table command. ANALYZE runs automatically when the health drops below the tidb_auto_analyze_ratio threshold.

Synopsis

ShowStmt

ShowStmt

ShowTargetFiltertable

ShowTargetFilterable

ShowLikeOrWhereOpt

ShowLikeOrWhereOpt

Examples

Load example data and run ANALYZE:

CREATE TABLE t1 ( id INT NOT NULL PRIMARY KEY auto_increment, b INT NOT NULL, pad VARBINARY(255), INDEX(b) ); INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM dual; INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000; SELECT SLEEP(1); ANALYZE TABLE t1; SHOW STATS_HEALTHY; # should be 100% healthy
... mysql> SHOW STATS_HEALTHY; +---------+------------+----------------+---------+ | Db_name | Table_name | Partition_name | Healthy | +---------+------------+----------------+---------+ | test | t1 | | 100 | +---------+------------+----------------+---------+ 1 row in set (0.00 sec)

Perform a bulk update deleting approximately 30% of the records. Check the health of the statistics:

DELETE FROM t1 WHERE id BETWEEN 101010 AND 201010; # delete about 30% of records SHOW STATS_HEALTHY;
mysql> SHOW STATS_HEALTHY; +---------+------------+----------------+---------+ | Db_name | Table_name | Partition_name | Healthy | +---------+------------+----------------+---------+ | test | t1 | | 50 | +---------+------------+----------------+---------+ 1 row in set (0.00 sec)

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also