STATISTICS table provides
information about table indexes.
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.
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.
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
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name' AND table_schema = 'db_name' SHOW INDEX FROM tbl_name FROM db_name