📣
TiDB Cloud Premium is now in public preview. Unlimited growth, instant elasticity, advanced security for enterprise workloads. Try it out →
​
Sign InStart for Free
AI
Developer
Best Practices
API
​
Sign InStart for Free

SHOW STATISTICS

Displays statistical information about tables and their columns. Statistics help the query optimizer make better decisions about query execution plans by providing information about data distribution, row counts, and distinct values.

TiDB Cloud Lake automatically generates statistics during data insertion. You can use this command to inspect the statistics and compare them with actual data to identify any discrepancies that might affect query performance.

Syntax

SHOW STATISTICS [ FROM DATABASE <database_name> | FROM TABLE <database_name>.<table_name> ]
ParameterDescription
FROM DATABASEShows statistics for all tables in the specified database.
FROM TABLEShows statistics for the specified table only.

If no parameter is specified, the command returns statistics for all tables in the current database.

Output Columns

The command returns the following columns for each column in each table:

ColumnDescription
databaseThe database name.
tableThe table name.
column_nameThe column name.
stats_row_countThe accumulated number of rows considered in statistics. Since stats are updated on inserts but not decremented on deletes, this number can be greater than actual_row_count.
actual_row_countThe actual number of rows in the table under the current snapshot.
distinct_countEstimated number of distinct values (NDV), computed from HyperLogLog.
null_countNumber of NULL values in the column.
avg_sizeAverage size in bytes of each value in the column.

Examples

Show Statistics for Current Database

CREATE DATABASE test_db;
USE test_db;

CREATE TABLE t1 (id INT, name VARCHAR(50));
INSERT INTO t1 VALUES (1, 'Alice'), (2, 'Bob');

SHOW STATISTICS;

Output:

database  table  column_name  stats_row_count  actual_row_count  distinct_count  null_count  avg_size
test_db   t1     id           2                2                 2               0           4
test_db   t1     name         2                2                 2               0           16

Show Statistics for a Specific Table

CREATE TABLE t2 (age INT, city VARCHAR(50));
INSERT INTO t2 VALUES (25, 'New York'), (30, 'London');

SHOW STATISTICS FROM TABLE test_db.t2;

Output:

database  table  column_name  stats_row_count  actual_row_count  distinct_count  null_count  avg_size
test_db   t2     age          2                2                 2               0           4
test_db   t2     city         2                2                 2               0           19

Show Statistics for All Tables in a Database

SHOW STATISTICS FROM DATABASE test_db;

This will show statistics for all tables (t1 and t2) in the test_db database.

Was this page helpful?