STATISTICS table provides
information about table indexes.
STATISTICS that represent
table statistics hold cached values. The
system variable defines the period of time before cached table
statistics expire. The default is 86400 seconds (24 hours). If
there are no cached statistics or statistics have expired,
statistics are retrieved from storage engines when querying table
statistics columns. To update cached values at any time for a
given table, use
ANALYZE TABLE. To
always retrieve the latest statistics directly from storage
For more information, see
Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
variable is enabled,
TABLE may fail because it cannot update statistics
tables in the data dictionary, which use
TABLE operations that update the key distribution,
failure may occur even if the operation updates the table itself
(for example, if it is a
MyISAM table). To
obtain the updated distribution statistics, set
STATISTICS table has these
The name of the catalog to which the table containing the index belongs. This value is always
The name of the schema (database) to which the table containing the index belongs.
The name of the table containing the index.
0 if the index cannot contain duplicates, 1 if it can.
The name of the schema (database) to which the index belongs.
The name of the index. If the index is the primary key, the name is always
The column sequence number in the index, starting with 1.
The column name. See also the description for the
How the column is sorted in the index. This can have values
An estimate of the number of unique values in the index. To update this number, run
ANALYZE TABLEor (for
MyISAMtables) myisamchk -a.
CARDINALITYis counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.
The index prefix. That is, the number of indexed characters if the column is only partly indexed,
NULLif the entire column is indexed.Note
Prefix limits are measured in bytes. However, prefix lengths for index specifications in
ALTER TABLE, and
CREATE INDEXstatements are interpreted as number of characters for nonbinary string types (
TEXT) and number of bytes for binary string types (
BLOB). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.
For additional information about index prefixes, see Section 8.3.5, “Column Indexes”, and Section 13.1.15, “CREATE INDEX Statement”.
Indicates how the key is packed.
NULLif it is not.
YESif the column may contain
The index method used (
Information about the index not described in its own column, such as
disabledif the index is disabled.
Any comment provided for the index with a
COMMENTattribute when the index was created.
Whether the index is visible to the optimizer. See Section 8.3.12, “Invisible Indexes”.
MySQL 8.0.13 and higher supports functional key parts (see Functional Key Parts), which affects both the
For a nonfunctional key part,
COLUMN_NAMEindicates the column indexed by the key part and
For a functional key part,
EXPRESSIONindicates the expression for the key part.
There is no standard
INFORMATION_SCHEMAtable for indexes. The MySQL column list is similar to what SQL Server 2000 returns for
sp_statistics, except that
OWNERare replaced with
Information about table indexes is also available from the
SHOW INDEX statement. See
Section 188.8.131.52, “SHOW INDEX Statement”. The following statements are
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name' AND table_schema = 'db_name' SHOW INDEX FROM tbl_name FROM db_name
In MySQL 8.0.30 and later, information about generated invisible
primary key columns is visible in this table by default. You can
cause such information to be hidden by setting
= OFF. For more information, see
Section 184.108.40.206, “Generated Invisible Primary Keys”.