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 statement. ANALYZE runs automatically when the health drops below the tidb_auto_analyze_ratio threshold.
Currently, the SHOW STATS_HEALTHY statement returns the following columns:
| Column name | Description |
|---|---|
Db_name | The database name |
Table_name | The table name |
Partition_name | The partition name |
Healthy | The healthy percentage between 0 and 100 |
Synopsis
- ShowStatsHealthyStmt
- ShowLikeOrWhere
ShowStatsHealthyStmt ::=
"SHOW" "STATS_HEALTHY" ShowLikeOrWhere?
ShowLikeOrWhere ::=
"LIKE" SimpleExpr
| "WHERE" Expression
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.