Table of Contents
INFORMATION_SCHEMA CHARACTER_SETS TableINFORMATION_SCHEMA COLLATIONS TableINFORMATION_SCHEMA
COLLATION_CHARACTER_SET_APPLICABILITY TableINFORMATION_SCHEMA COLUMNS TableINFORMATION_SCHEMA COLUMN_PRIVILEGES TableINFORMATION_SCHEMA ENGINES TableINFORMATION_SCHEMA EVENTS TableINFORMATION_SCHEMA FILES TableINFORMATION_SCHEMA GLOBAL_STATUS and
SESSION_STATUS
TablesINFORMATION_SCHEMA GLOBAL_VARIABLES and
SESSION_VARIABLES
TablesINFORMATION_SCHEMA KEY_COLUMN_USAGE TableINFORMATION_SCHEMA OPTIMIZER_TRACE TableINFORMATION_SCHEMA PARAMETERS TableINFORMATION_SCHEMA PARTITIONS TableINFORMATION_SCHEMA PLUGINS TableINFORMATION_SCHEMA PROCESSLIST TableINFORMATION_SCHEMA PROFILING TableINFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS TableINFORMATION_SCHEMA ROUTINES TableINFORMATION_SCHEMA SCHEMATA TableINFORMATION_SCHEMA SCHEMA_PRIVILEGES TableINFORMATION_SCHEMA STATISTICS TableINFORMATION_SCHEMA TABLES TableINFORMATION_SCHEMA TABLESPACES TableINFORMATION_SCHEMA TABLE_CONSTRAINTS TableINFORMATION_SCHEMA TABLE_PRIVILEGES TableINFORMATION_SCHEMA TRIGGERS TableINFORMATION_SCHEMA USER_PRIVILEGES TableINFORMATION_SCHEMA VIEWS TableINFORMATION_SCHEMA Tables for
InnoDBINFORMATION_SCHEMA INNODB_CMP and
INNODB_CMP_RESET TablesINFORMATION_SCHEMA INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET TablesINFORMATION_SCHEMA INNODB_CMPMEM and
INNODB_CMPMEM_RESET TablesINFORMATION_SCHEMA INNODB_TRX TableINFORMATION_SCHEMA INNODB_LOCKS TableINFORMATION_SCHEMA INNODB_LOCK_WAITS TableINFORMATION_SCHEMA INNODB_SYS_TABLES TableINFORMATION_SCHEMA INNODB_SYS_INDEXES TableINFORMATION_SCHEMA INNODB_SYS_COLUMNS TableINFORMATION_SCHEMA INNODB_SYS_FIELDS TableINFORMATION_SCHEMA INNODB_SYS_FOREIGN TableINFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS TableINFORMATION_SCHEMA INNODB_SYS_TABLESTATS ViewINFORMATION_SCHEMA INNODB_BUFFER_PAGE TableINFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU TableINFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS TableINFORMATION_SCHEMA INNODB_METRICS TableINFORMATION_SCHEMA INNODB_FT_CONFIG TableINFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD
TableINFORMATION_SCHEMA INNODB_FT_INDEX_TABLE TableINFORMATION_SCHEMA INNODB_FT_INDEX_CACHE TableINFORMATION_SCHEMA INNODB_FT_INSERTED TableINFORMATION_SCHEMA INNODB_FT_DELETED TableINFORMATION_SCHEMA INNODB_FT_BEING_DELETED TableSHOW Statements
INFORMATION_SCHEMA provides access to database
metadata, information about the MySQL server
such as the name of a database or table, the data type of a column,
or access privileges. Other terms that are sometimes used for this
information are data dictionary and
system catalog.
INFORMATION_SCHEMA Database
INFORMATION_SCHEMA is a database within each
MySQL instance, the place that stores information about all the
other databases that the MySQL server maintains. The
INFORMATION_SCHEMA database contains several
read-only tables. They are actually views, not base tables, so there
are no files associated with them, and you cannot set triggers on
them. Also, there is no database directory with that name.
Although you can select INFORMATION_SCHEMA as the
default database with a USE
statement, you can only read the contents of tables, not perform
INSERT,
UPDATE, or
DELETE operations on them.
Here is an example of a statement that retrieves information from
INFORMATION_SCHEMA:
mysql>SELECT table_name, table_type, engine->FROM information_schema.tables->WHERE table_schema = 'db5'->ORDER BY table_name;+------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | fk | BASE TABLE | InnoDB | | fk2 | BASE TABLE | InnoDB | | goto | BASE TABLE | MyISAM | | into | BASE TABLE | MyISAM | | k | BASE TABLE | MyISAM | | kurs | BASE TABLE | MyISAM | | loop | BASE TABLE | MyISAM | | pk | BASE TABLE | InnoDB | | t | BASE TABLE | MyISAM | | t2 | BASE TABLE | MyISAM | | t3 | BASE TABLE | MyISAM | | t7 | BASE TABLE | MyISAM | | tables | BASE TABLE | MyISAM | | v | VIEW | NULL | | v2 | VIEW | NULL | | v3 | VIEW | NULL | | v56 | VIEW | NULL | +------------+------------+--------+ 17 rows in set (0.01 sec)
Explanation: The statement requests a list of all the tables in
database db5, showing just three pieces of
information: the name of the table, its type, and its storage
engine.
The definition for character columns (for example,
TABLES.TABLE_NAME) is generally
VARCHAR( where N) CHARACTER SET
utf8N is at least 64.
MySQL uses the default collation for this character set
(utf8_general_ci) for all searches, sorts,
comparisons, and other string operations on such columns.
Because some MySQL objects are represented as files, searches in
INFORMATION_SCHEMA string columns can be affected
by file system case sensitivity. For more information, see
Section 10.1.7.9, “Collation and INFORMATION_SCHEMA Searches”.
INFORMATION_SCHEMA as Alternative to SHOW Statements
The SELECT ... FROM INFORMATION_SCHEMA statement
is intended as a more consistent way to provide access to the
information provided by the various
SHOW statements that MySQL supports
(SHOW DATABASES,
SHOW TABLES, and so forth). Using
SELECT has these advantages, compared
to SHOW:
It conforms to Codd's rules, because all access is done on tables.
You can use the familiar syntax of the
SELECT statement, and only need
to learn some table and column names.
The implementor need not worry about adding keywords.
You can filter, sort, concatenate, and transform the results
from INFORMATION_SCHEMA queries into whatever
format your application needs, such as a data structure or a
text representation to parse.
This technique is more interoperable with other database systems. For example, Oracle Database users are familiar with querying tables in the Oracle data dictionary.
Because SHOW is familiar and widely
used, the SHOW statements remain as
an alternative. In fact, along with the implementation of
INFORMATION_SCHEMA, there are enhancements to
SHOW as described in
Section 19.31, “Extensions to SHOW Statements”.
Each MySQL user has the right to access these tables, but can see
only the rows in the tables that correspond to objects for which the
user has the proper access privileges. In some cases (for example,
the ROUTINE_DEFINITION column in the
INFORMATION_SCHEMA.ROUTINES table),
users who have insufficient privileges see NULL.
These restrictions do not apply for
InnoDB tables; you can see them with
only the PROCESS privilege.
The same privileges apply to selecting information from
INFORMATION_SCHEMA and viewing the same
information through SHOW statements.
In either case, you must have some privilege on an object to see
information about it.
INFORMATION_SCHEMA queries that search for
information from more than one database might take a long time and
impact performance. To check the efficiency of a query, you can use
EXPLAIN. For information about using
EXPLAIN output to tune
INFORMATION_SCHEMA queries, see
Section 8.2.4, “Optimizing INFORMATION_SCHEMA Queries”.
The implementation for the INFORMATION_SCHEMA
table structures in MySQL follows the ANSI/ISO SQL:2003 standard
Part 11 Schemata. Our intent is approximate
compliance with SQL:2003 core feature F021 Basic
information schema.
Users of SQL Server 2000 (which also follows the standard) may
notice a strong similarity. However, MySQL has omitted many columns
that are not relevant for our implementation, and added columns that
are MySQL-specific. One such column is the ENGINE
column in the INFORMATION_SCHEMA.TABLES
table.
Although other DBMSs use a variety of names, like
syscat or system, the standard
name is INFORMATION_SCHEMA.
To avoid using any name that is reserved in the standard or in DB2,
SQL Server, or Oracle, we changed the names of some columns marked
“MySQL extension”. (For example, we changed
COLLATION to TABLE_COLLATION
in the TABLES table.) See the list of
reserved words near the end of this article:
http://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5.
INFORMATION_SCHEMA Reference Sections
The following sections describe each of the tables and columns in
INFORMATION_SCHEMA. For each column, there are
three pieces of information:
“INFORMATION_SCHEMA Name”
indicates the name for the column in the
INFORMATION_SCHEMA table. This corresponds to
the standard SQL name unless the “Remarks” field
says “MySQL extension.”
“SHOW Name”
indicates the equivalent field name in the closest
SHOW statement, if there is one.
“Remarks” provides additional information where
applicable. If this field is NULL, it means
that the value of the column is always NULL.
If this field says “MySQL extension,” the column is
a MySQL extension to standard SQL.
Many sections indicate what SHOW
statement is equivalent to a SELECT
that retrieves information from
INFORMATION_SCHEMA. For
SHOW statements that display
information for the default database if you omit a FROM
clause, you can often
select information for the default database by adding an
db_nameAND TABLE_SCHEMA = SCHEMA() condition to the
WHERE clause of a query that retrieves
information from an INFORMATION_SCHEMA table.
For information about INFORMATION_SCHEMA tables
specific to the InnoDB storage engine,
see Section 19.30, “INFORMATION_SCHEMA Tables for
InnoDB”.
For answers to questions that are often asked concerning the
INFORMATION_SCHEMA database, see
Section B.7, “MySQL 5.6 FAQ: INFORMATION_SCHEMA”.
The CHARACTER_SETS table provides
information about available character sets.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
CHARACTER_SET_NAME | Charset | |
DEFAULT_COLLATE_NAME | Default collation | |
DESCRIPTION | Description | MySQL extension |
MAXLEN | Maxlen | MySQL extension |
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS [WHERE CHARACTER_SET_NAME LIKE 'wild'] SHOW CHARACTER SET [LIKE 'wild']
The COLLATIONS table provides
information about collations for each character set.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
COLLATION_NAME | Collation | |
CHARACTER_SET_NAME | Charset | MySQL extension |
ID | Id | MySQL extension |
IS_DEFAULT | Default | MySQL extension |
IS_COMPILED | Compiled | MySQL extension |
SORTLEN | Sortlen | MySQL extension |
COLLATION_NAME is the collation name.
CHARACTER_SET_NAME is the name of the
character set with which the collation is associated.
ID is the collation ID.
IS_DEFAULT indicates whether the collation
is the default for its character set.
IS_COMPILED indicates whether the character
set is compiled into the server.
SORTLEN is related to the amount of memory
required to sort strings expressed in the character set.
Collation information is also available from the
SHOW COLLATION statement. The
following statements are equivalent:
SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS [WHERE COLLATION_NAME LIKE 'wild'] SHOW COLLATION [LIKE 'wild']
The
COLLATION_CHARACTER_SET_APPLICABILITY
table indicates what character set is applicable for what
collation. The columns are equivalent to the first two display
fields that we get from SHOW
COLLATION.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
COLLATION_NAME | Collation | |
CHARACTER_SET_NAME | Charset |
The COLUMNS table provides
information about columns in tables.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | Field | |
ORDINAL_POSITION | see notes | |
COLUMN_DEFAULT | Default | |
IS_NULLABLE | Null | |
DATA_TYPE | Type | |
CHARACTER_MAXIMUM_LENGTH | Type | |
CHARACTER_OCTET_LENGTH | ||
NUMERIC_PRECISION | Type | |
NUMERIC_SCALE | Type | |
DATETIME_PRECISION | Type | |
CHARACTER_SET_NAME | ||
COLLATION_NAME | Collation | |
COLUMN_TYPE | Type | MySQL extension |
COLUMN_KEY | Key | MySQL extension |
EXTRA | Extra | MySQL extension |
PRIVILEGES | Privileges | MySQL extension |
COLUMN_COMMENT | Comment | MySQL extension |
Notes:
In SHOW, the
Type display includes values from several
different COLUMNS columns.
ORDINAL_POSITION is necessary because you
might want to say ORDER BY
ORDINAL_POSITION. Unlike
SHOW,
SELECT does not have automatic
ordering.
CHARACTER_OCTET_LENGTH should be the same
as CHARACTER_MAXIMUM_LENGTH, except for
multi-byte character sets.
CHARACTER_SET_NAME can be derived from
Collation. For example, if you say
SHOW FULL COLUMNS FROM t, and you see in
the Collation column a value of
latin1_swedish_ci, the character set is
what is before the first underscore:
latin1.
DATETIME_PRECISION was added in MySQL
5.6.4.
The following statements are nearly equivalent:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' [AND table_schema = 'db_name'] [AND column_name LIKE 'wild'] SHOW COLUMNS FROMtbl_name[FROMdb_name] [LIKE 'wild']
The COLUMN_PRIVILEGES table provides
information about column privileges. This information comes from
the mysql.columns_priv grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
GRANTEE | '
value | |
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
In the output from
SHOW FULL
COLUMNS, the privileges are all in one field and in
lowercase, for example,
select,insert,update,references. In
COLUMN_PRIVILEGES, there is one
privilege per row, in uppercase.
PRIVILEGE_TYPE can contain one (and only
one) of these values: SELECT,
INSERT,
UPDATE,
REFERENCES.
If the user has GRANT OPTION
privilege, IS_GRANTABLE should be
YES. Otherwise,
IS_GRANTABLE should be
NO. The output does not list
GRANT OPTION as a separate
privilege.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES SHOW GRANTS ...
The PLUGINS table provides
information about storage engines.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
ENGINE | Engine | MySQL extension |
SUPPORT | Support | MySQL extension |
COMMENT | Comment | MySQL extension |
TRANSACTIONS | Transactions | MySQL extension |
XA | XA | MySQL extension |
SAVEPOINTS | Savepoints | MySQL extension |
Notes:
The ENGINES table is a
nonstandard table.
See also Section 13.7.5.17, “SHOW ENGINES Syntax”.
The EVENTS table provides information
about scheduled events, which are discussed in
Section 18.4, “Using the Event Scheduler”. The SHOW Name values
correspond to column names of the SHOW
EVENTS statement.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
EVENT_CATALOG | def, MySQL extension | |
EVENT_SCHEMA | Db | MySQL extension |
EVENT_NAME | Name | MySQL extension |
DEFINER | Definer | MySQL extension |
TIME_ZONE | Time zone | MySQL extension |
EVENT_BODY | MySQL extension | |
EVENT_DEFINITION | MySQL extension | |
EVENT_TYPE | Type | MySQL extension |
EXECUTE_AT | Execute at | MySQL extension |
INTERVAL_VALUE | Interval value | MySQL extension |
INTERVAL_FIELD | Interval field | MySQL extension |
SQL_MODE | MySQL extension | |
STARTS | Starts | MySQL extension |
ENDS | Ends | MySQL extension |
STATUS | Status | MySQL extension |
ON_COMPLETION | MySQL extension | |
CREATED | MySQL extension | |
LAST_ALTERED | MySQL extension | |
LAST_EXECUTED | MySQL extension | |
EVENT_COMMENT | MySQL extension | |
ORIGINATOR | Originator | MySQL extension |
CHARACTER_SET_CLIENT | character_set_client | MySQL extension |
COLLATION_CONNECTION | collation_connection | MySQL extension |
DATABASE_COLLATION | Database Collation | MySQL extension |
Notes:
The EVENTS table is a nonstandard
table.
EVENT_CATALOG: The value of this column is
always def.
EVENT_SCHEMA: The name of the schema
(database) to which this event belongs.
EVENT_NAME: The name of the event.
DEFINER: The account of the user who
created the event, in
'
format.
user_name'@'host_name'
TIME_ZONE: The event time zone, which is
the time zone used for scheduling the event and that is in
effect within the event as it executes. The default value is
SYSTEM.
EVENT_BODY: The language used for the
statements in the event's DO
clause; in MySQL 5.6, this is always
SQL.
This column is not to be confused with the column of the same
name (now named EVENT_DEFINITION) that
existed in earlier MySQL versions.
EVENT_DEFINITION: The text of the SQL
statement making up the event's
DO clause; in other words, the
statement executed by this event.
EVENT_TYPE: The event repetition type,
either ONE TIME (transient) or
RECURRING (repeating).
EXECUTE_AT: For a one-time event, this is
the DATETIME value specified in
the AT clause of the
CREATE EVENT statement used to
create the event, or of the last ALTER
EVENT statement that modified the event. The value
shown in this column reflects the addition or subtraction of
any INTERVAL value included in the event's
AT clause. For example, if an event is
created using ON SCHEDULE AT CURRENT_TIMESTAMP +
'1:6' DAY_HOUR, and the event was created at
2006-02-09 14:05:30, the value shown in this column would be
'2006-02-10 20:05:30'.
If the event's timing is determined by an
EVERY clause instead of an
AT clause (that is, if the event is
recurring), the value of this column is
NULL.
INTERVAL_VALUE: For recurring events, this
column contains the numeric portion of the event's
EVERY clause.
For a one-time event (that is, an event whose timing is
determined by an AT clause), this column is
NULL.
INTERVAL_FIELD: For recurring events, this
column contains the units portion of the
EVERY clause governing the timing of the
event. Thus, this column contains a value such as
'YEAR',
'QUARTER', 'DAY', and so
on.
For a one-time event (that is, an event whose timing is
determined by an AT clause), this column is
NULL.
SQL_MODE: The SQL mode in effect at the
time the event was created or altered.
STARTS: For a recurring event whose
definition includes a STARTS clause, this
column contains the corresponding
DATETIME value. As with the
EXECUTE_AT column, this value resolves any
expressions used.
If there is no STARTS clause affecting the
timing of the event, this column is NULL
ENDS: For a recurring event whose
definition includes a ENDS clause, this
column contains the corresponding
DATETIME value. As with the
EXECUTE_AT column, this value resolves any
expressions used.
If there is no ENDS clause affecting the
timing of the event, this column is NULL.
STATUS: One of the three values
ENABLED, DISABLED, or
SLAVESIDE_DISABLED.
SLAVESIDE_DISABLED indicates that the
creation of the event occurred on another MySQL server acting
as a replication master and was replicated to the current
MySQL server which is acting as a slave, but the event is not
presently being executed on the slave. See
Section 16.4.1.10, “Replication of Invoked Features”, for more
information.
ON_COMPLETION: One of the two values
PRESERVE or NOT
PRESERVE.
CREATED: The date and time when the event
was created. This is a DATETIME
value.
LAST_ALTERED: The date and time when the
event was last modified. This is a
DATETIME value. If the event
has not been modified since its creation, this column holds
the same value as the CREATED column.
LAST_EXECUTED: The date and time when the
event last executed. A DATETIME
value. If the event has never executed, this column is
NULL.
LAST_EXECUTED indicates when the event
started. As a result, the ENDS column is
never less than LAST_EXECUTED.
EVENT_COMMENT: The text of a comment, if
the event has one. If not, the value of this column is an
empty string.
ORIGINATOR: The server ID of the MySQL
server on which the event was created; used in replication.
The default value is 0.
CHARACTER_SET_CLIENT is the session value
of the character_set_client
system variable when the event was created.
COLLATION_CONNECTION is the session value
of the collation_connection
system variable when the event was created.
DATABASE_COLLATION is the collation of the
database with which the event is associated.
Example: Suppose that the user
jon@ghidora creates an event named
e_daily, and then modifies it a few minutes
later using an ALTER EVENT
statement, as shown here:
DELIMITER |
CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Saves total number of sessions then clears the table each day'
DO
BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END |
DELIMITER ;
ALTER EVENT e_daily
ENABLED;(Note that comments can span multiple lines.)
This user can then run the following
SELECT statement, and obtain the
output shown:
mysql>SELECT * FROM INFORMATION_SCHEMA.EVENTS>WHERE EVENT_NAME = 'e_daily'>AND EVENT_SCHEMA = 'myschema'\G*************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: test EVENT_NAME: e_daily DEFINER: paul@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: BEGIN INSERT INTO site_activity.totals (time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM site_activity.sessions; DELETE FROM site_activity.sessions; END EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: DAY SQL_MODE: STARTS: 2008-09-03 12:13:39 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2008-09-03 12:13:39 LAST_ALTERED: 2008-09-03 12:13:39 LAST_EXECUTED: NULL EVENT_COMMENT: Saves total number of sessions then clears the table each day ORIGINATOR: 1 CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci
Times in the EVENTS table are
displayed using the event time zone or the current session time
zone, as described in Section 18.4.4, “Event Metadata”.
See also Section 13.7.5.19, “SHOW EVENTS Syntax”.
The FILES table provides information
about the files in which MySQL tablespace data is stored.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
FILE_ID | MySQL extension | |
FILE_NAME | MySQL extension | |
FILE_TYPE | MySQL extension | |
TABLESPACE_NAME | MySQL extension | |
TABLE_CATALOG | MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
TABLE_NAME | MySQL extension | |
LOGFILE_GROUP_NAME | MySQL extension | |
LOGFILE_GROUP_NUMBER | MySQL extension | |
ENGINE | MySQL extension | |
FULLTEXT_KEYS | MySQL extension | |
DELETED_ROWS | MySQL extension | |
UPDATE_COUNT | MySQL extension | |
FREE_EXTENTS | MySQL extension | |
TOTAL_EXTENTS | MySQL extension | |
EXTENT_SIZE | MySQL extension | |
INITIAL_SIZE | MySQL extension | |
MAXIMUM_SIZE | MySQL extension | |
AUTOEXTEND_SIZE | MySQL extension | |
CREATION_TIME | MySQL extension | |
LAST_UPDATE_TIME | MySQL extension | |
LAST_ACCESS_TIME | MySQL extension | |
RECOVER_TIME | MySQL extension | |
TRANSACTION_COUNTER | MySQL extension | |
VERSION | MySQL extension | |
ROW_FORMAT | MySQL extension | |
TABLE_ROWS | MySQL extension | |
AVG_ROW_LENGTH | MySQL extension | |
DATA_LENGTH | MySQL extension | |
MAX_DATA_LENGTH | MySQL extension | |
INDEX_LENGTH | MySQL extension | |
DATA_FREE | MySQL extension | |
CREATE_TIME | MySQL extension | |
UPDATE_TIME | MySQL extension | |
CHECK_TIME | MySQL extension | |
CHECKSUM | MySQL extension | |
STATUS | MySQL extension | |
EXTRA | MySQL extension |
Notes:
FILE_ID column values are auto-generated.
FILE_NAME is the name of a data file
created by CREATE TABLESPACE or
ALTER TABLESPACE.
FILE_TYPE is the tablespace file type.
TABLESPACE_NAME is the name of the
tablespace with which the file is associated.
Currently, the value of the
TABLESPACE_CATALOG column is always
NULL.
TABLE_NAME is the name of the table with
which the file is associated, if any.
The EXTENT_SIZE is always
0.
There are no SHOW statements
associated with the FILES table.
The GLOBAL_STATUS
and SESSION_STATUS
tables provide information about server status variables. Their
contents correspond to the information produced by the
SHOW GLOBAL
STATUS and
SHOW SESSION
STATUS statements (see Section 13.7.5.36, “SHOW STATUS Syntax”).
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
| VARIABLE_NAME | Variable_name | |
| VARIABLE_VALUE | Value |
Notes:
The VARIABLE_VALUE column for each of these
tables is defined as VARCHAR(20480).
The
GLOBAL_VARIABLES
and
SESSION_VARIABLES
tables provide information about server status variables. Their
contents correspond to the information produced by the
SHOW GLOBAL
VARIABLES and
SHOW SESSION
VARIABLES statements (see
Section 13.7.5.40, “SHOW VARIABLES Syntax”).
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
| VARIABLE_NAME | Variable_name | |
| VARIABLE_VALUE | Value |
Notes:
The VARIABLE_VALUE column for each of these
tables is defined as VARCHAR(20480).
The KEY_COLUMN_USAGE table describes
which key columns have constraints.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
CONSTRAINT_CATALOG | def | |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | ||
ORDINAL_POSITION | ||
POSITION_IN_UNIQUE_CONSTRAINT | ||
REFERENCED_TABLE_SCHEMA | ||
REFERENCED_TABLE_NAME | ||
REFERENCED_COLUMN_NAME |
Notes:
If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.
The value of ORDINAL_POSITION is the
column's position within the constraint, not the column's
position within the table. Column positions are numbered
beginning with 1.
The value of POSITION_IN_UNIQUE_CONSTRAINT
is NULL for unique and primary-key
constraints. For foreign-key constraints, it is the ordinal
position in key of the table that is being referenced.
Suppose that there are two tables name t1
and t3 that have the following definitions:
CREATE TABLE t1
(
s1 INT,
s2 INT,
s3 INT,
PRIMARY KEY(s3)
) ENGINE=InnoDB;
CREATE TABLE t3
(
s1 INT,
s2 INT,
s3 INT,
KEY(s1),
CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;
For those two tables, the
KEY_COLUMN_USAGE table has two
rows:
One row with CONSTRAINT_NAME =
'PRIMARY',
TABLE_NAME = 't1',
COLUMN_NAME = 's3',
ORDINAL_POSITION =
1,
POSITION_IN_UNIQUE_CONSTRAINT =
NULL.
One row with CONSTRAINT_NAME =
'CO', TABLE_NAME =
't3', COLUMN_NAME =
's2',
ORDINAL_POSITION =
1,
POSITION_IN_UNIQUE_CONSTRAINT =
1.
The OPTIMIZER_TRACE table provides
information produced by the optimizer tracing capability. To
enable tracking, use the
optimizer_trace system variable.
For details, see
MySQL
Internals: Tracing the Optimizer.
The OPTIMIZER_TRACE table was added in MySQL
5.6.3.
The PARAMETERS table provides
information about stored procedure and function parameters, and
about return values for stored functions. Parameter information is
similar to the contents of the param_list
column in the mysql.proc table.
INFORMATION_SCHEMA Name | mysql.proc Name | Remarks |
|---|---|---|
SPECIFIC_CATALOG | def | |
SPECIFIC_SCHEMA | db | routine database |
SPECIFIC_NAME | name | routine name |
ORDINAL_POSITION | 1, 2, 3, ... for parameters, 0 for function RETURNS
clause | |
PARAMETER_MODE | IN, OUT, INOUT
(NULL for RETURNS) | |
PARAMETER_NAME | parameter name (NULL for RETURNS) | |
DATA_TYPE | same as for COLUMNS table | |
CHARACTER_MAXIMUM_LENGTH | same as for COLUMNS table | |
CHARACTER_OCTET_LENGTH | same as for COLUMNS table | |
NUMERIC_PRECISION | same as for COLUMNS table | |
NUMERIC_SCALE | same as for COLUMNS table | |
DATETIME_PRECISION | same as for COLUMNS table | |
CHARACTER_SET_NAME | same as for COLUMNS table | |
COLLATION_NAME | same as for COLUMNS table | |
DTD_IDENTIFIER | same as for COLUMNS table | |
ROUTINE_TYPE | type | same as for ROUTINES table |
Notes:
For successive parameters of a stored procedure or function,
the ORDINAL_POSITION values are 1, 2, 3,
and so forth. For a stored function, there is also a row that
describes the data type for the RETURNS
clause. The return value is not a true parameter, so the row
that describes it has these unique characteristics:
The ORDINAL_POSITION value is 0.
The PARAMETER_NAME and
PARAMETER_MODE values are
NULL because the return value has no
name and the mode does not apply.
DATETIME_PRECISION was added in MySQL
5.6.4.
The PARTITIONS table provides
information about table partitions. See
Chapter 17, Partitioning, for more information about
partitioning tables.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
TABLE_NAME | MySQL extension | |
PARTITION_NAME | MySQL extension | |
SUBPARTITION_NAME | MySQL extension | |
PARTITION_ORDINAL_POSITION | MySQL extension | |
SUBPARTITION_ORDINAL_POSITION | MySQL extension | |
PARTITION_METHOD | MySQL extension | |
SUBPARTITION_METHOD | MySQL extension | |
PARTITION_EXPRESSION | MySQL extension | |
SUBPARTITION_EXPRESSION | MySQL extension | |
PARTITION_DESCRIPTION | MySQL extension | |
TABLE_ROWS | MySQL extension | |
AVG_ROW_LENGTH | MySQL extension | |
DATA_LENGTH | MySQL extension | |
MAX_DATA_LENGTH | MySQL extension | |
INDEX_LENGTH | MySQL extension | |
DATA_FREE | MySQL extension | |
CREATE_TIME | MySQL extension | |
UPDATE_TIME | MySQL extension | |
CHECK_TIME | MySQL extension | |
CHECKSUM | MySQL extension | |
PARTITION_COMMENT | MySQL extension | |
NODEGROUP | MySQL extension | |
TABLESPACE_NAME | MySQL extension |
Notes:
The PARTITIONS table is a
nonstandard table.
Each record in this table corresponds to an individual partition or subpartition of a partitioned table.
TABLE_CATALOG: This column is always
def.
TABLE_SCHEMA: This column contains the name
of the database to which the table belongs.
TABLE_NAME: This column contains the name
of the table containing the partition.
PARTITION_NAME: The name of the partition.
SUBPARTITION_NAME: If the
PARTITIONS table record
represents a subpartition, then this column contains the name
of subpartition; otherwise it is NULL.
PARTITION_ORDINAL_POSITION: All partitions
are indexed in the same order as they are defined, with
1 being the number assigned to the first
partition. The indexing can change as partitions are added,
dropped, and reorganized; the number shown is this column
reflects the current order, taking into account any indexing
changes.
SUBPARTITION_ORDINAL_POSITION:
Subpartitions within a given partition are also indexed and
reindexed in the same manner as partitions are indexed within
a table.
PARTITION_METHOD: One of the values
RANGE, LIST,
HASH, LINEAR HASH,
KEY, or LINEAR KEY; that
is, one of the available partitioning types as discussed in
Section 17.2, “Partitioning Types”.
SUBPARTITION_METHOD: One of the values
HASH, LINEAR HASH,
KEY, or LINEAR KEY; that
is, one of the available subpartitioning types as discussed in
Section 17.2.6, “Subpartitioning”.
PARTITION_EXPRESSION: This is the
expression for the partitioning function used in the
CREATE TABLE or
ALTER TABLE statement that
created the table's current partitioning scheme.
For example, consider a partitioned table created in the
test database using this statement:
CREATE TABLE tp (
c1 INT,
c2 INT,
c3 VARCHAR(25)
)
PARTITION BY HASH(c1 + c2)
PARTITIONS 4;
The PARTITION_EXPRESSION column in a
PARTITIONS table record for a partition from this table
displays c1 + c2, as shown here:
mysql>SELECT DISTINCT PARTITION_EXPRESSION>FROM INFORMATION_SCHEMA.PARTITIONS>WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test';+----------------------+ | PARTITION_EXPRESSION | +----------------------+ | c1 + c2 | +----------------------+ 1 row in set (0.09 sec)
SUBPARTITION_EXPRESSION: This works in the
same fashion for the subpartitioning expression that defines
the subpartitioning for a table as
PARTITION_EXPRESSION does for the
partitioning expression used to define a table's partitioning.
If the table has no subpartitions, then this column is
NULL.
PARTITION_DESCRIPTION: This column is used
for RANGE and LIST partitions. For a RANGE
partition, it contains the value set in the partition's
VALUES LESS THAN clause, which can be
either an integer or MAXVALUE. For a
LIST partition, this column contains the
values defined in the partition's VALUES IN
clause, which is a comma-separated list of integer values.
For partitions whose PARTITION_METHOD is
other than RANGE or
LIST, this column is always
NULL.
TABLE_ROWS: The number of table rows in the
partition.
For partitioned InnoDB tables,
the row count given in the TABLE_ROWS
column is only an estimated value used in SQL optimization,
and may not always be exact.
AVG_ROW_LENGTH: The average length of the
rows stored in this partition or subpartition, in bytes.
This is the same as DATA_LENGTH divided by
TABLE_ROWS.
DATA_LENGTH: The total length of all rows
stored in this partition or subpartition, in bytes—that
is, the total number of bytes stored in the partition or
subpartition.
MAX_DATA_LENGTH: The maximum number of
bytes that can be stored in this partition or subpartition.
INDEX_LENGTH: The length of the index file
for this partition or subpartition, in bytes.
DATA_FREE: The number of bytes allocated to
the partition or subpartition but not used.
CREATE_TIME: The time of the partition's or
subpartition's creation.
UPDATE_TIME: The time that the partition or
subpartition was last modified.
CHECK_TIME: The last time that the table to
which this partition or subpartition belongs was checked.
Some storage engines do not update this time; for tables
using these storage engines, this value is always
NULL.
CHECKSUM: The checksum value, if any;
otherwise, this column is NULL.
PARTITION_COMMENT: This column contains the
text of any comment made for the partition.
Prior to MySQL 5.6.6, the display width of this column was 80
characters, and partition comments which exceeded this length
were truncated to fit. As of MySQL 5.6.6, the maximum length
for a partition comment is defined as 1024 characters, and the
display width of the PARTITION_COMMENT
column is increased to 1024 characters to match this limit
(Bug #11748924, Bug #37728).
The default value for this column is an empty string.
NODEGROUP: This is the nodegroup to which
the partition belongs. This is relevant only to MySQL Cluster
tables; otherwise the value of this column is always
0.
TABLESPACE_NAME: This column contains the
name of the tablespace to which the partition belongs.
Currently, the value of this column is always
DEFAULT.
A nonpartitioned table has one record in
INFORMATION_SCHEMA.PARTITIONS;
however, the values of the PARTITION_NAME,
SUBPARTITION_NAME,
PARTITION_ORDINAL_POSITION,
SUBPARTITION_ORDINAL_POSITION,
PARTITION_METHOD,
SUBPARTITION_METHOD,
PARTITION_EXPRESSION,
SUBPARTITION_EXPRESSION, and
PARTITION_DESCRIPTION columns are all
NULL. (The
PARTITION_COMMENT column in this case is
blank.)
The PLUGINS table provides
information about server plugins.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
PLUGIN_NAME | Name | MySQL extension |
PLUGIN_VERSION | MySQL extension | |
PLUGIN_STATUS | Status | MySQL extension |
PLUGIN_TYPE | Type | MySQL extension |
PLUGIN_TYPE_VERSION | MySQL extension | |
PLUGIN_LIBRARY | Library | MySQL extension |
PLUGIN_LIBRARY_VERSION | MySQL extension | |
PLUGIN_AUTHOR | MySQL extension | |
PLUGIN_DESCRIPTION | MySQL extension | |
PLUGIN_LICENSE | MySQL extension | |
LOAD_OPTION | MySQL extension |
Notes:
The PLUGINS table is a
nonstandard table.
PLUGIN_NAME is the name used to refer to
the plugin in statements such as INSTALL
PLUGIN and UNINSTALL
PLUGIN.
PLUGIN_VERSION is the version from the
plugin's general type descriptor.
PLUGIN_STATUS indicates the plugin status,
one of ACTIVE, INACTIVE,
DISABLED, or DELETED.
PLUGIN_TYPE indicates the type of plugin,
such as STORAGE ENGINE,
INFORMATION_SCHEMA, or
AUTHENTICATION.
PLUGIN_TYPE_VERSION is the version from the
plugin's type-specific descriptor.
PLUGIN_LIBRARY is the name of the plugin
shared object file. This is the name used to refer to the
plugin file in statements such as INSTALL
PLUGIN and UNINSTALL
PLUGIN. This file is located in the directory named
by the plugin_dir system
variable. If the library name is NULL, the
plugin is compiled in and cannot be uninstalled with
UNINSTALL PLUGIN.
PLUGIN_LIBRARY_VERSION indicates the plugin
API interface version.
PLUGIN_AUTHOR names the plugin author.
PLUGIN_DESCRIPTION provides a short
description of the plugin.
PLUGIN_LICENSE indicates how the plugin is
licensed; for example, GPL.
LOAD_OPTION indicates how the plugin was
loaded. The value is OFF,
ON, FORCE, or
FORCE_PLUS_PERMANENT. See
Section 5.1.8.1, “Installing and Uninstalling Plugins”.
For plugins installed with INSTALL
PLUGIN, the PLUGIN_NAME and
PLUGIN_LIBRARY values are also registered in
the mysql.plugin table.
These statements are equivalent:
SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_LIBRARY, PLUGIN_LICENSE FROM INFORMATION_SCHEMA.PLUGINS; SHOW PLUGINS;
For information about plugin data structures that form the basis
of the information in the PLUGINS
table, see Section 22.2, “The MySQL Plugin API”.
Plugin information is also available using the
SHOW PLUGINS statement. See
Section 13.7.5.26, “SHOW PLUGINS Syntax”.
The PROCESSLIST table provides
information about which threads are running.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
ID | Id | MySQL extension |
USER | User | MySQL extension |
HOST | Host | MySQL extension |
DB | db | MySQL extension |
COMMAND | Command | MySQL extension |
TIME | Time | MySQL extension |
STATE | State | MySQL extension |
INFO | Info | MySQL extension |
For an extensive description of the table columns, see
Section 13.7.5.30, “SHOW PROCESSLIST Syntax”.
Notes:
The PROCESSLIST table is a
nonstandard table.
Like the output from the corresponding
SHOW statement, the
PROCESSLIST table will only show
information about your own threads, unless you have the
PROCESS privilege, in which
case you will see information about other threads, too. As an
anonymous user, you cannot see any rows at all.
If an SQL statement refers to
INFORMATION_SCHEMA.PROCESSLIST,
then MySQL will populate the entire table once, when statement
execution begins, so there is read consistency during the
statement. There is no read consistency for a multi-statement
transaction, though.
Process information is also available from the
performance_schema.threads table.
However, access to threads does
not require a mutex and has minimal impact on server
performance.
INFORMATION_SCHEMA.PROCESSLIST
and SHOW PROCESSLIST have
negative performance consequences because they require a
mutex. threads also shows
information about background threads, which
INFORMATION_SCHEMA.PROCESSLIST
and SHOW PROCESSLIST do not.
This means that threads can be
used to monitor activity the other thread information sources
cannot.
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST SHOW FULL PROCESSLIST
The PROFILING table provides
statement profiling information. Its contents correspond to the
information produced by the SHOW
PROFILES and SHOW PROFILE
statements (see Section 13.7.5.32, “SHOW PROFILES Syntax”). The table is
empty unless the profiling
session variable is set to 1.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
QUERY_ID | Query_ID | |
SEQ | | |
STATE | Status | |
DURATION | Duration | |
CPU_USER | CPU_user | |
CPU_SYSTEM | CPU_system | |
CONTEXT_VOLUNTARY | Context_voluntary | |
CONTEXT_INVOLUNTARY | Context_involuntary | |
BLOCK_OPS_IN | Block_ops_in | |
BLOCK_OPS_OUT | Block_ops_out | |
MESSAGES_SENT | Messages_sent | |
MESSAGES_RECEIVED | Messages_received | |
PAGE_FAULTS_MAJOR | Page_faults_major | |
PAGE_FAULTS_MINOR | Page_faults_minor | |
SWAPS | Swaps | |
SOURCE_FUNCTION | Source_function | |
SOURCE_FILE | Source_file | |
SOURCE_LINE | Source_line |
Notes:
QUERY_ID is a numeric statement identifier.
SEQ is a sequence number indicating the
display order for rows with the same
QUERY_ID value.
STATE is the profiling state to which the
row measurements apply.
DURATION indicates how long statement
execution remained in the given state, in seconds.
CPU_USER and CPU_SYSTEM
indicate user and system CPU use, in seconds.
CONTEXT_VOLUNTARY and
CONTEXT_INVOLUNTARY indicate how many
voluntary and involuntary context switches occurred.
BLOCK_OPS_IN and
BLOCK_OPS_OUT indicate the number of block
input and output operations.
MESSAGES_SENT and
MESSAGES_RECEIVED indicate the number of
communication messages sent and received.
PAGE_FAULTS_MAJOR and
PAGE_FAULTS_MINOR indicate the number of
major and minor page faults.
SWAPS indicates how many swaps occurred.
SOURCE_FUNCTION,
SOURCE_FILE, and
SOURCE_LINE provide information indicating
where in the source code the profiled state executes.
The REFERENTIAL_CONSTRAINTS table
provides information about foreign keys.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
| CONSTRAINT_CATALOG | def | |
| CONSTRAINT_SCHEMA | ||
| CONSTRAINT_NAME | ||
| UNIQUE_CONSTRAINT_CATALOG | def | |
| UNIQUE_CONSTRAINT_SCHEMA | ||
| UNIQUE_CONSTRAINT_NAME | ||
| MATCH_OPTION | ||
| UPDATE_RULE | ||
| DELETE_RULE | ||
| TABLE_NAME | ||
| REFERENCED_TABLE_NAME |
Notes:
TABLE_NAME has the same value as
TABLE_NAME in
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.
CONSTRAINT_SCHEMA and
CONSTRAINT_NAME identify the foreign key.
UNIQUE_CONSTRAINT_SCHEMA,
UNIQUE_CONSTRAINT_NAME, and
REFERENCED_TABLE_NAME identify the
referenced key.
The only valid value at this time for
MATCH_OPTION is NONE.
The possible values for UPDATE_RULE or
DELETE_RULE are CASCADE,
SET NULL, SET DEFAULT,
RESTRICT, NO ACTION.
The ROUTINES table provides
information about stored routines (both procedures and functions).
The ROUTINES table does not include
user-defined functions (UDFs).
The column named “mysql.proc name”
indicates the mysql.proc table column that
corresponds to the
INFORMATION_SCHEMA.ROUTINES table
column, if any.
INFORMATION_SCHEMA Name | mysql.proc Name | Remarks |
|---|---|---|
SPECIFIC_NAME | specific_name | |
ROUTINE_CATALOG | def | |
ROUTINE_SCHEMA | db | |
ROUTINE_NAME | name | |
ROUTINE_TYPE | type | {PROCEDURE|FUNCTION} |
DATA_TYPE | same as for COLUMNS table | |
CHARACTER_MAXIMUM_LENGTH | same as for COLUMNS table | |
CHARACTER_OCTET_LENGTH | same as for COLUMNS table | |
NUMERIC_PRECISION | same as for COLUMNS table | |
NUMERIC_SCALE | same as for COLUMNS table | |
DATETIME_PRECISION | same as for COLUMNS table | |
CHARACTER_SET_NAME | same as for COLUMNS table | |
COLLATION_NAME | same as for COLUMNS table | |
DTD_IDENTIFIER | data type descriptor | |
ROUTINE_BODY | SQL | |
ROUTINE_DEFINITION | body_utf8 | |
EXTERNAL_NAME | NULL | |
EXTERNAL_LANGUAGE | language | NULL |
PARAMETER_STYLE | SQL | |
IS_DETERMINISTIC | is_deterministic | |
SQL_DATA_ACCESS | sql_data_access | |
SQL_PATH | NULL | |
SECURITY_TYPE | security_type | |
CREATED | created | |
LAST_ALTERED | modified | |
SQL_MODE | sql_mode | MySQL extension |
ROUTINE_COMMENT | comment | MySQL extension |
DEFINER | definer | MySQL extension |
CHARACTER_SET_CLIENT | MySQL extension | |
COLLATION_CONNECTION | MySQL extension | |
DATABASE_COLLATION | MySQL extension |
Notes:
MySQL calculates EXTERNAL_LANGUAGE thus:
If mysql.proc.language='SQL',
EXTERNAL_LANGUAGE is
NULL
Otherwise, EXTERNAL_LANGUAGE is what is
in mysql.proc.language. However, we do
not have external languages yet, so it is always
NULL.
CHARACTER_SET_CLIENT is the session value
of the character_set_client
system variable when the routine was created.
COLLATION_CONNECTION is the session value
of the collation_connection
system variable when the routine was created.
DATABASE_COLLATION is the collation of the
database with which the routine is associated.
The DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
DATETIME_PRECISION,
CHARACTER_SET_NAME, and
COLLATION_NAME columns provide information
about the data type for the RETURNS clause
of stored functions. If a stored routine is a stored
procedure, these columns all are NULL.
DATETIME_PRECISION was added in MySQL
5.6.4.
Information about stored function RETURNS
data types is also available in the
PARAMETERS table. The return
value data type row for a function can be identified as the
row that has an ORDINAL_POSITION value of
0.
A schema is a database, so the
SCHEMATA table provides information
about databases.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
CATALOG_NAME | def | |
SCHEMA_NAME | Database | |
DEFAULT_CHARACTER_SET_NAME | ||
DEFAULT_COLLATION_NAME | ||
SQL_PATH | NULL |
The following statements are equivalent:
SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA [WHERE SCHEMA_NAME LIKE 'wild'] SHOW DATABASES [LIKE 'wild']
The SCHEMA_PRIVILEGES table provides
information about schema (database) privileges. This information
comes from the mysql.db grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
GRANTEE | '
value, MySQL extension | |
TABLE_CATALOG | def, MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
PRIVILEGE_TYPE | MySQL extension | |
IS_GRANTABLE | MySQL extension |
Notes:
This is a nonstandard table. It takes its values from the
mysql.db table.
The STATISTICS table provides
information about table indexes.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | = Database | |
TABLE_NAME | Table | |
NON_UNIQUE | Non_unique | |
INDEX_SCHEMA | = Database | |
INDEX_NAME | Key_name | |
SEQ_IN_INDEX | Seq_in_index | |
COLUMN_NAME | Column_name | |
COLLATION | Collation | |
CARDINALITY | Cardinality | |
SUB_PART | Sub_part | MySQL extension |
PACKED | Packed | MySQL extension |
NULLABLE | Null | MySQL extension |
INDEX_TYPE | Index_type | MySQL extension |
COMMENT | Comment | MySQL extension |
Notes:
There is no standard table for indexes. The preceding list is
similar to what SQL Server 2000 returns for
sp_statistics, except that we replaced the
name QUALIFIER with
CATALOG and we replaced the name
OWNER with SCHEMA.
Clearly, the preceding table and the output from
SHOW INDEX are derived from the
same parent. So the correlation is already close.
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name' AND table_schema = 'db_name' SHOW INDEX FROMtbl_nameFROMdb_name
The TABLES table provides information
about tables in databases.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | Table_... | |
TABLE_NAME | Table_... | |
TABLE_TYPE | ||
ENGINE | Engine | MySQL extension |
VERSION | Version | The version number of the table's .frm file, MySQL
extension |
ROW_FORMAT | Row_format | MySQL extension |
TABLE_ROWS | Rows | MySQL extension |
AVG_ROW_LENGTH | Avg_row_length | MySQL extension |
DATA_LENGTH | Data_length | MySQL extension |
MAX_DATA_LENGTH | Max_data_length | MySQL extension |
INDEX_LENGTH | Index_length | MySQL extension |
DATA_FREE | Data_free | MySQL extension |
AUTO_INCREMENT | Auto_increment | MySQL extension |
CREATE_TIME | Create_time | MySQL extension |
UPDATE_TIME | Update_time | MySQL extension |
CHECK_TIME | Check_time | MySQL extension |
TABLE_COLLATION | Collation | MySQL extension |
CHECKSUM | Checksum | MySQL extension |
CREATE_OPTIONS | Create_options | MySQL extension |
TABLE_COMMENT | Comment | MySQL extension |
Notes:
TABLE_SCHEMA and
TABLE_NAME are a single field in a
SHOW display, for example
Table_in_db1.
TABLE_TYPE should be BASE
TABLE or VIEW. Currently, the
TABLES table does not list
TEMPORARY tables.
For partitioned tables, the ENGINE column
shows the name of the storage engine used by all partitions.
(Previously, this column showed PARTITION
for such tables.)
The TABLE_ROWS column is
NULL if the table is in the
INFORMATION_SCHEMA database.
For InnoDB tables, the row count
is only a rough estimate used in SQL optimization. (This is
also true if the InnoDB table is
partitioned.)
The DATA_FREE column shows the free space
in bytes for InnoDB tables.
We have nothing for the table's default character set.
TABLE_COLLATION is close, because collation
names begin with a character set name.
The CREATE_OPTIONS column shows
partitioned if the table is partitioned.
The following statements are equivalent:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' [AND table_name LIKE 'wild'] SHOW TABLES FROMdb_name[LIKE 'wild']
The TABLESPACES table provides
information about active tablespaces.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLESPACE_NAME | MySQL extension | |
ENGINE | MySQL extension | |
TABLESPACE_TYPE | MySQL extension | |
LOGFILE_GROUP_NAME | MySQL extension | |
EXTENT_SIZE | MySQL extension | |
AUTOEXTEND_SIZE | MySQL extension | |
MAXIMUM_SIZE | MySQL extension | |
NODEGROUP_ID | MySQL extension | |
TABLESPACE_COMMENT | MySQL extension |
The TABLE_CONSTRAINTS table describes
which tables have constraints.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
CONSTRAINT_CATALOG | def | |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
TABLE_SCHEMA | ||
TABLE_NAME | ||
CONSTRAINT_TYPE |
Notes:
The CONSTRAINT_TYPE value can be
UNIQUE, PRIMARY KEY, or
FOREIGN KEY.
The UNIQUE and PRIMARY
KEY information is about the same as what you get
from the Key_name field in the output from
SHOW INDEX when the
Non_unique field is 0.
The CONSTRAINT_TYPE column can contain one
of these values: UNIQUE, PRIMARY
KEY, FOREIGN KEY,
CHECK. This is a
CHAR (not
ENUM) column. The
CHECK value is not available until we
support CHECK.
The TABLE_PRIVILEGES table provides
information about table privileges. This information comes from
the mysql.tables_priv grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
GRANTEE | '
value | |
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
PRIVILEGE_TYPE can contain one (and only
one) of these values: SELECT,
INSERT,
UPDATE,
REFERENCES,
ALTER,
INDEX,
DROP,
CREATE VIEW.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES SHOW GRANTS ...
The TRIGGERS table provides
information about triggers. You can see results only for databases
and tables for which you have the
TRIGGER privilege.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TRIGGER_CATALOG | def | |
TRIGGER_SCHEMA | ||
TRIGGER_NAME | Trigger | |
EVENT_MANIPULATION | Event | |
EVENT_OBJECT_CATALOG | def | |
EVENT_OBJECT_SCHEMA | ||
EVENT_OBJECT_TABLE | Table | |
ACTION_ORDER | 0 | |
ACTION_CONDITION | NULL | |
ACTION_STATEMENT | Statement | |
ACTION_ORIENTATION | ROW | |
ACTION_TIMING | Timing | |
ACTION_REFERENCE_OLD_TABLE | NULL | |
ACTION_REFERENCE_NEW_TABLE | NULL | |
ACTION_REFERENCE_OLD_ROW | OLD | |
ACTION_REFERENCE_NEW_ROW | NEW | |
CREATED | NULL (0) | |
SQL_MODE | MySQL extension | |
DEFINER | MySQL extension | |
CHARACTER_SET_CLIENT | MySQL extension | |
COLLATION_CONNECTION | MySQL extension | |
DATABASE_COLLATION | MySQL extension |
Notes:
The TRIGGER_SCHEMA and
TRIGGER_NAME columns contain the name of
the database in which the trigger occurs and the trigger name,
respectively.
The EVENT_MANIPULATION column contains one
of the values 'INSERT',
'DELETE', or 'UPDATE'.
As noted in Section 18.3, “Using Triggers”, every trigger is
associated with exactly one table. The
EVENT_OBJECT_SCHEMA and
EVENT_OBJECT_TABLE columns contain the
database in which this table occurs, and the table's name.
The ACTION_ORDER column contains the
ordinal position of the trigger's action within the list of
all similar triggers on the same table. Currently, this value
is always 0, because it is not possible to
have more than one trigger with the same
EVENT_MANIPULATION and
ACTION_TIMING on the same table.
The ACTION_STATEMENT column contains the
statement to be executed when the trigger is invoked. This is
the same as the text displayed in the
Statement column of the output from
SHOW TRIGGERS. Note that this
text uses UTF-8 encoding.
The ACTION_ORIENTATION column always
contains the value 'ROW'.
The ACTION_TIMING column contains one of
the two values 'BEFORE' or
'AFTER'.
The columns ACTION_REFERENCE_OLD_ROW and
ACTION_REFERENCE_NEW_ROW contain the old
and new column identifiers, respectively. This means that
ACTION_REFERENCE_OLD_ROW always contains
the value 'OLD' and
ACTION_REFERENCE_NEW_ROW always contains
the value 'NEW'.
The SQL_MODE column shows the server SQL
mode that was in effect at the time when the trigger was
created (and thus which remains in effect for this trigger
whenever it is invoked, regardless of the current
server SQL mode). The possible range of values for
this column is the same as that of the
sql_mode system variable. See
Section 5.1.7, “Server SQL Modes”.
The DEFINER column indicates who defined
the trigger.
CHARACTER_SET_CLIENT is the session value
of the character_set_client
system variable when the trigger was created.
COLLATION_CONNECTION is the session value
of the collation_connection
system variable when the trigger was created.
DATABASE_COLLATION is the collation of the
database with which the trigger is associated.
The following columns currently always contain
NULL: ACTION_CONDITION,
ACTION_REFERENCE_OLD_TABLE,
ACTION_REFERENCE_NEW_TABLE, and
CREATED.
Example, using the ins_sum trigger defined in
Section 18.3, “Using Triggers”:
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER: me@localhost
The USER_PRIVILEGES table provides
information about global privileges. This information comes from
the mysql.user grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
GRANTEE | '
value, MySQL extension | |
TABLE_CATALOG | def, MySQL extension | |
PRIVILEGE_TYPE | MySQL extension | |
IS_GRANTABLE | MySQL extension |
Notes:
This is a nonstandard table. It takes its values from the
mysql.user table.
The VIEWS table provides information
about views in databases. You must have the
SHOW VIEW privilege to access this
table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
VIEW_DEFINITION | ||
CHECK_OPTION | ||
IS_UPDATABLE | ||
DEFINER | ||
SECURITY_TYPE | ||
CHARACTER_SET_CLIENT | MySQL extension | |
COLLATION_CONNECTION | MySQL extension |
Notes:
The VIEW_DEFINITION column has most of what
you see in the Create Table field that
SHOW CREATE VIEW produces. Skip
the words before SELECT and
skip the words WITH CHECK OPTION. Suppose
that the original statement was:
CREATE VIEW v AS SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1 WITH CHECK OPTION;
Then the view definition looks like this:
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
The CHECK_OPTION column has a value of
NONE, CASCADE, or
LOCAL.
MySQL sets a flag, called the view updatability flag, at
CREATE VIEW time. The flag is
set to YES (true) if
UPDATE and
DELETE (and similar operations)
are legal for the view. Otherwise, the flag is set to
NO (false). The
IS_UPDATABLE column in the
VIEWS table displays the status
of this flag. It means that the server always knows whether a
view is updatable. If the view is not updatable, statements
such UPDATE,
DELETE, and
INSERT are illegal and will be
rejected. (Note that even if a view is updatable, it might not
be possible to insert into it; for details, refer to
Section 13.1.16, “CREATE VIEW Syntax”.)
The DEFINER column indicates who defined
the view. SECURITY_TYPE has a value of
DEFINER or INVOKER.
CHARACTER_SET_CLIENT is the session value
of the character_set_client
system variable when the view was created.
COLLATION_CONNECTION is the session value
of the collation_connection
system variable when the view was created.
MySQL lets you use different
sql_mode settings to tell the
server the type of SQL syntax to support. For example, you might
use the ANSI SQL mode to ensure
MySQL correctly interprets the standard SQL concatenation
operator, the double bar (||), in your queries.
If you then create a view that concatenates items, you might worry
that changing the sql_mode
setting to a value different from
ANSI could cause the view to
become invalid. But this is not the case. No matter how you write
out a view definition, MySQL always stores it the same way, in a
canonical form. Here is an example that shows how the server
changes a double bar concatenation operator to a
CONCAT() function:
mysql>SET sql_mode = 'ANSI';Query OK, 0 rows affected (0.00 sec) mysql>CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;Query OK, 0 rows affected (0.00 sec) mysql>SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS->WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';+----------------------------------+ | VIEW_DEFINITION | +----------------------------------+ | select concat('a','b') AS `col1` | +----------------------------------+ 1 row in set (0.00 sec)
The advantage of storing a view definition in canonical form is
that changes made later to the value of
sql_mode will not affect the
results from the view. However an additional consequence is that
comments prior to SELECT are
stripped from the definition by the server.
INFORMATION_SCHEMA INNODB_CMP and
INNODB_CMP_RESET TablesINFORMATION_SCHEMA INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET TablesINFORMATION_SCHEMA INNODB_CMPMEM and
INNODB_CMPMEM_RESET TablesINFORMATION_SCHEMA INNODB_TRX TableINFORMATION_SCHEMA INNODB_LOCKS TableINFORMATION_SCHEMA INNODB_LOCK_WAITS TableINFORMATION_SCHEMA INNODB_SYS_TABLES TableINFORMATION_SCHEMA INNODB_SYS_INDEXES TableINFORMATION_SCHEMA INNODB_SYS_COLUMNS TableINFORMATION_SCHEMA INNODB_SYS_FIELDS TableINFORMATION_SCHEMA INNODB_SYS_FOREIGN TableINFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS TableINFORMATION_SCHEMA INNODB_SYS_TABLESTATS ViewINFORMATION_SCHEMA INNODB_BUFFER_PAGE TableINFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU TableINFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS TableINFORMATION_SCHEMA INNODB_METRICS TableINFORMATION_SCHEMA INNODB_FT_CONFIG TableINFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD
TableINFORMATION_SCHEMA INNODB_FT_INDEX_TABLE TableINFORMATION_SCHEMA INNODB_FT_INDEX_CACHE TableINFORMATION_SCHEMA INNODB_FT_INSERTED TableINFORMATION_SCHEMA INNODB_FT_DELETED TableINFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
The InnoDB tables related to the
InnoDB storage engine serve two purposes:
You can monitor ongoing InnoDB activity, to
detect inefficiencies before they turn into issues, or to
troubleshoot performance and capacity issues that do occur. As
your database becomes bigger and busier, running up against the
limits of your hardware capacity, you monitor and tune these
aspects to keep the database running smoothly. The monitoring
information deals with:
InnoDB table compression, a feature whose
use depends on a balance between I/O reduction, CPU usage,
buffer pool management, and how much compression is possible
for your data.
Transactions and locks, features that balance high performance for a single operation, against the ability to run multiple operations concurrently. (Transactions are the high-level, user-visible aspect of concurrency. Locks are the low-level mechanism that transactions use to avoid reading or writing unreliable data.)
You can extract information about schema objects managed by
InnoDB, using the
INNODB_SYS_* tables. This information comes
from the InnoDB data dictionary, which cannot
be queried directly like regular InnoDB
tables. Traditionally, you would get this type of information
using the techniques from Section 14.2.5.4, “SHOW ENGINE INNODB
STATUS and the InnoDB Monitors”,
setting up InnoDB monitors and parsing the
output from the SHOW ENGINE INNODB STATUS
command. The InnoDB interface offers a
simpler, familiar technique to access this data.
The INNODB_CMP and
INNODB_CMP_RESET tables contain status
information on operations related to
compressed
InnoDB tables.
Table 19.1. Columns of INNODB_CMP and
INNODB_CMP_RESET
| Column name | Description |
|---|---|
PAGE_SIZE | Compressed page size in bytes. |
COMPRESS_OPS | Number of times a B-tree page of the size PAGE_SIZE
has been compressed. Pages are compressed whenever an empty
page is created or the space for the uncompressed modification
log runs out. |
COMPRESS_OPS_OK | Number of times a B-tree page of the size PAGE_SIZE
has been successfully compressed. This count should never
exceed COMPRESS_OPS. |
COMPRESS_TIME | Total time in seconds spent in attempts to compress B-tree pages of the
size PAGE_SIZE. |
UNCOMPRESS_OPS | Number of times a B-tree page of the size PAGE_SIZE
has been uncompressed. B-tree pages are uncompressed whenever
compression fails or at first access when the uncompressed
page does not exist in the buffer pool. |
UNCOMPRESS_TIME | Total time in seconds spent in uncompressing B-tree pages of the size
PAGE_SIZE. |
Notes:
Use these tables to measure the effectiveness of
InnoDB table compression in your database.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.2.5.3.1.3, “Using the Compression Information Schema Tables”.
The INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET tables contain
status information on operations related to
compressed
InnoDB tables and indexes, divided according to
the index to help you evaluate the performance and usefulness of
compression for specific tables.
For a compressed InnoDB table, both the table
data and all the secondary indexes are compressed. In this
context, the table data is treated as just another index, one that
happens to contain all the columns: the
clustered index.
Table 19.2. Columns of INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET
| Column name | Description |
|---|---|
DATABASE_NAME | Database containing the applicable table. |
TABLE_NAME | Table to monitor for compression statistics. |
INDEX_NAME | Index to monitor for compression statistics. |
COMPRESS_OPS | Number of compression operations attempted. Pages are compressed whenever an empty page is created or the space for the uncompressed modification log runs out. |
COMPRESS_OPS_OK | Number of successful compression operations. |
COMPRESS_TIME | Total amount of CPU time, in seconds, used for compressing data in this index. |
UNCOMPRESS_OPS | Number of uncompression operations performed. Compressed
InnoDB pages are uncompressed whenever
compression fails, or the first time a compressed page is
accessed in the buffer pool and the uncompressed page does not
exist. |
UNCOMPRESS_TIME | Total amount of CPU time, in seconds, used for uncompressing data in this index. |
Notes:
Use these tables to measure the effectiveness of
InnoDB table compression for specific
tables, indexes, or both.
You must have the PROCESS privilege to
query these tables.
For usage information, see Section 14.2.5.3.1.3, “Using the Compression Information Schema Tables”.
The INNODB_CMPMEM and
INNODB_CMPMEM_RESET tables contain status
information on compressed pages within the
InnoDB buffer pool.
Table 19.3. Columns of INNODB_CMPMEM and INNODB_CMPMEM_RESET
| Column name | Description |
|---|---|
PAGE_SIZE | Block size in bytes. Each record of this table describes blocks of this size. |
PAGES_USED | Number of blocks of the size PAGE_SIZE that are
currently in use. |
PAGES_FREE | Number of blocks of the size PAGE_SIZE that are
currently available for allocation. This column shows the
external fragmentation in the memory pool. Ideally, these
numbers should be at most 1. |
RELOCATION_OPS | Number of times a block of the size PAGE_SIZE has
been relocated. The buddy system can relocate the allocated
“buddy neighbor” of a freed block when it tries
to form a bigger freed block. Reading from the table
INNODB_CMPMEM_RESET resets this count. |
RELOCATION_TIME | Total time in microseconds spent in relocating blocks of the size
PAGE_SIZE. Reading from the table
INNODB_CMPMEM_RESET resets this count. |
Notes:
Use these tables to measure the effectiveness of
InnoDB table compression in your database.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.2.5.3.1.3, “Using the Compression Information Schema Tables”.
The INNODB_TRX table contains information about
every transaction currently executing inside
InnoDB, including whether the transaction is
waiting for a lock, when the transaction started, and the SQL
statement the transaction is executing.
Table 19.4. INNODB_TRX Columns
| Column name | Description |
|---|---|
TRX_ID | Unique transaction ID number, internal to InnoDB.
(Starting in MySQL 5.6, these IDs are not created for
transactions that are read-only and non-locking. See
Section 14.2.5.2.2, “Optimizations for Read-Only Transactions” for details.) |
TRX_WEIGHT | The weight of a transaction, reflecting (but not necessarily the exact
count of) the number of rows altered and the number of rows
locked by the transaction. To resolve a deadlock,
InnoDB selects the transaction with the
smallest weight as the “victim” to rollback.
Transactions that have changed non-transactional tables are
considered heavier than others, regardless of the number of
altered and locked rows. |
TRX_STATE | Transaction execution state. One of RUNNING,
LOCK WAIT, ROLLING BACK
or COMMITTING. |
TRX_STARTED | Transaction start time. |
TRX_REQUESTED_LOCK_ID | ID of the lock the transaction is currently waiting for (if
TRX_STATE is LOCK WAIT,
otherwise NULL). Details about the lock can
be found by joining with INNODB_LOCKS on
LOCK_ID. |
TRX_WAIT_STARTED | Time when the transaction started waiting on the lock (if
TRX_STATE is LOCK WAIT,
otherwise NULL). |
TRX_MYSQL_THREAD_ID | MySQL thread ID. Can be used for joining with
PROCESSLIST on ID. See
Section 14.2.5.3.4.3, “Possible Inconsistency with PROCESSLIST”. |
TRX_QUERY | The SQL query that is being executed by the transaction. |
TRX_OPERATION_STATE | The transaction's current operation, or NULL. |
TRX_TABLES_IN_USE | The number of InnoDB tables used while processing the current SQL statement of this transaction. |
TRX_TABLES_LOCKED | Number of InnoDB tables that currently have any locks. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.) |
TRX_LOCK_STRUCTS | The number of locks reserved by the transaction. |
TRX_LOCK_MEMORY_BYTES | Total size taken up by the lock structures of this transaction in memory. |
TRX_ROWS_LOCKED | Approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction. |
TRX_ROWS_MODIFIED | The number of modified and inserted rows in this transaction. |
TRX_CONCURRENCY_TICKETS | A value indicating how much work the current transaction can do before
being swapped out, as specified by the
innodb_concurrency_tickets option. |
TRX_ISOLATION_LEVEL | The isolation level of the current transaction. |
TRX_UNIQUE_CHECKS | Whether unique checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
TRX_FOREIGN_KEY_CHECKS | Whether foreign key checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
TRX_LAST_FOREIGN_KEY_ERROR | Detailed error message for last FK error, or NULL. |
TRX_ADAPTIVE_HASH_LATCHED | Whether or not the adaptive hash index is locked by the current transaction. (Only a single transaction at a time can modify the adaptive hash index.) |
TRX_ADAPTIVE_HASH_TIMEOUT | Whether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no AHI contention, this value remains zero and statements reserve the latch until they finish. During times of contention, it counts down to zero, and statements release the latch immediately after each row lookup. |
TRX_IS_READ_ONLY | A value of 1 indicates the transaction is read-only. (5.6.4 and up.) |
TRX_AUTOCOMMIT_NON_LOCKING |
A value of 1 indicates the transaction is a
|
Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.2.5.3.2.4, “Using the Transaction Information Schema Tables”.
The INNODB_LOCKS table contains information
about each lock that an InnoDB transaction has
requested but not yet acquired, and each lock that a transaction
holds that is blocking another transaction.
Table 19.5. INNODB_LOCKS Columns
| Column name | Description |
|---|---|
LOCK_ID | Unique lock ID number, internal to InnoDB. Treat it
as an opaque string. Although LOCK_ID
currently contains TRX_ID, the format of
the data in LOCK_ID is not guaranteed to
remain the same in future releases. Do not write programs that
parse the LOCK_ID value. |
LOCK_TRX_ID | ID of the transaction holding this lock. Details about the transaction
can be found by joining with INNODB_TRX on
TRX_ID. |
LOCK_MODE | Mode of the lock. One of S, X,
IS, IX,
S_GAP, X_GAP,
IS_GAP, IX_GAP, or
AUTO_INC for shared, exclusive, intention
shared, intention exclusive row locks, shared and exclusive
gap locks, intention shared and intention exclusive gap locks,
and auto-increment table level lock, respectively. Refer to
the sections Section 14.2.4.2, “InnoDB Lock Modes” and
Section 14.2.4.1, “The InnoDB Transaction Model and Locking” for information on
InnoDB locking. |
LOCK_TYPE | Type of the lock. One of RECORD or
TABLE for record (row) level or table level
locks, respectively. |
LOCK_TABLE | Name of the table that has been locked or contains locked records. |
LOCK_INDEX | Name of the index if LOCK_TYPE='RECORD', otherwise
NULL. |
LOCK_SPACE | Tablespace ID of the locked record if
LOCK_TYPE='RECORD', otherwise
NULL. |
LOCK_PAGE | Page number of the locked record if
LOCK_TYPE='RECORD', otherwise
NULL. |
LOCK_REC | Heap number of the locked record within the page if
LOCK_TYPE='RECORD', otherwise
NULL. |
LOCK_DATA | Primary key of the locked record if
LOCK_TYPE='RECORD', otherwise
NULL. This column contains the value(s) of
the primary key column(s) in the locked row, formatted as a
valid SQL string (ready to be copied to SQL commands). If
there is no primary key then the InnoDB
internal unique row ID number is used. When the page
containing the locked record is not in the buffer pool (in the
case that it was paged out to disk while the lock was held),
InnoDB does not fetch the page from disk,
to avoid unnecessary disk operations. Instead,
LOCK_DATA is set to
NULL. |
Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.2.5.3.2.4, “Using the Transaction Information Schema Tables”.
The INNODB_LOCK_WAITS table contains one or
more rows for each blocked InnoDB transaction,
indicating the lock it has requested and any locks that are
blocking that request.
Table 19.6. INNODB_LOCK_WAITS Columns
| Column name | Description |
|---|---|
REQUESTING_TRX_ID | ID of the requesting transaction. |
REQUESTED_LOCK_ID | ID of the lock for which a transaction is waiting. Details about the
lock can be found by joining with
INNODB_LOCKS on LOCK_ID. |
BLOCKING_TRX_ID | ID of the blocking transaction. |
BLOCKING_LOCK_ID | ID of a lock held by a transaction blocking another transaction from
proceeding. Details about the lock can be found by joining
with INNODB_LOCKS on
LOCK_ID. |
Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.2.5.3.2.4, “Using the Transaction Information Schema Tables”.
The INNODB_SYS_TABLES table provides status
information about InnoDB tables, equivalent to
the information from the SYS_TABLES table in
the InnoDB data dictionary.
Table 19.7. INNODB_SYS_TABLES Columns
| Column name | Description |
|---|---|
TABLE_ID | An identifier for each InnoDB table that is unique
across all databases in the instance. |
NAME | The name of the table. Preceded by the database name where appropriate,
for example test/t1.
InnoDB system table names are in all
uppercase. Names of databases and user tables are in the
same case as they were originally defined, possibly
influenced by the
lower_case_table_names
setting. |
FLAG | 0 = InnoDB system table, 1 = user table. |
N_COLS | The number of columns in the table. |
SPACE | An identifier for the tablespace where the table resides. 0 means the
InnoDB
system
tablespace. Any other number represents a table
created in
file-per-table
mode with a separate .ibd file. This
identifier stays the same after a
TRUNCATE TABLE statement.
Other than the zero value, this identifier is unique for
tables across all the databases in the instance. |
Notes:
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
You must have the PROCESS privilege to
query this table.
The INNODB_SYS_INDEXES table provides status
information about InnoDB indexes, equivalent to
the information from the SYS_INDEXES table in
the InnoDB data dictionary.
Table 19.8. INNODB_SYS_INDEXES Columns
| Column name | Description |
|---|---|
INDEX_ID | An identifier for each index that is unique across all the databases in an instance. |
NAME | The name of the index. User-created indexes have names in all lowercase.
Indexes created implicitly by InnoDB
have names in all lowercase. The index names are not
necessarily unique. Indexes created implicitly by
InnoDB have consistent names:
PRIMARY for a primary key index,
GEN_CLUST_INDEX for the index
representing a primary key when one is not specified,
ID_IND, FOR_IND for
validating a foreign key constraint, and
REF_IND.
|
TABLE_ID | An identifier representing the table associated with the index; the same
value from INNODB_SYS_TABLES.TABLE_ID. |
TYPE | A numeric identifier signifying the kind of index, in the range 0..3. |
N_FIELDS | The number of columns in the index key. For the
GEN_CLUST_INDEX indexes, this value is
0 because the index is created using an artificial value
rather than a real table column. |
PAGE_NO | |
SPACE | An identifier for the tablespace where the index resides. 0 means the
InnoDB
system
tablespace. Any other number represents a table
created in
file-per-table
mode with a separate .ibd file. This
identifier stays the same after a
TRUNCATE TABLE statement.
Because all indexes for a table reside in the same
tablespace as the table, this value is not necessarily
unique. |
Notes:
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
You must have the PROCESS privilege to
query this table.
The INNODB_SYS_COLUMNS table provides status
information about InnoDB table columns,
equivalent to the information from the
SYS_COLUMNS table in the
InnoDB data dictionary.
Table 19.9. INNODB_SYS_COLUMNS Columns
| Column name | Description |
|---|---|
TABLE_ID | An identifier representing the table associated with the column; the
same value from
INNODB_SYS_TABLES.TABLE_ID. |
NAME | The name of each column in each table. These names can be uppercase or
lowercase depending on the
lower_case_table_names
setting. There are no special system-reserved names for
columns. |
POS | The ordinal position of the column within the table, starting from 0 and incrementing sequentially. When a column is dropped, the remaining columns are reordered so that the sequence has no gaps. |
MTYPE | A numeric identifier for the column type. 1 =
VARCHAR, 2 = CHAR, 3
= FIXBINARY, 4 =
BINARY, 5 = BLOB, 6
= INT, 7 =
SYS_CHILD, 8 = SYS,
9 = FLOAT, 10 =
DOUBLE, 11 =
DECIMAL, 12 =
VARMYSQL, 13 =
MYSQL. |
PRTYPE | The InnoDB “precise type”, a binary
value with bits representing MySQL data type, character
set code, and nullability. |
LEN | The column length, for example 4 for INT and 8 for
BIGINT. For character columns in
multi-byte character sets, this length value is the
maximum length in bytes needed to represent a definition
such as
VARCHAR(;
that is, it might be
2*,
3*, and so
on depending on the character encoding. |
Notes:
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
You must have the PROCESS privilege to
query this table.
The INNODB_SYS_FIELDS table provides status
information about the key columns (fields) of
InnoDB indexes, equivalent to the information
from the SYS_FIELDS table in the
InnoDB data dictionary.
Table 19.10. INNODB_SYS_FIELDS Columns
| Column name | Description |
|---|---|
INDEX_ID | An identifier for the index associated with this key field, using the
same value as in
INNODB_SYS_INDEXES.INDEX_ID. |
NAME | The name of the original column from the table, using the same value as
in INNODB_SYS_COLUMNS.NAME. |
POS | The ordinal position of the key field within the index, starting from 0 and incrementing sequentially. When a column is dropped, the remaining columns are reordered so that the sequence has no gaps. |
Notes:
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
You must have the PROCESS privilege to
query this table.
The INNODB_SYS_FOREIGN table provides status
information about InnoDB
foreign keys, equivalent
to the information from the SYS_FOREIGN table
in the InnoDB data dictionary.
Table 19.11. INNODB_SYS_FOREIGN Columns
| Column name | Description |
|---|---|
ID | The name (not a numeric value) of the foreign key index. Preceded by the
database name, for example,
test/products_fk. |
FOR_NAME | The name of the child table in this foreign key relationship. |
REF_NAME | The name of the parent table in this foreign key relationship. |
N_COLS | The number of columns in the foreign key index. |
TYPE | A collection of bit flags with information about the foreign key column,
ORed together. 1 = ON DELETE CASCADE, 2
= ON UPDATE SET NULL, 4 = ON
UPDATE CASCADE, 8 = ON UPDATE
SET NULL, 16 = ON DELETE NO
ACTION, 32 = ON UPDATE NO ACTION. |
Notes:
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
You must have the PROCESS privilege to
query this table.
The INNODB_SYS_FOREIGN_COLS table provides
status information about the columns of InnoDB
foreign keys, equivalent to the information from the
SYS_FOREIGN_COLS table in the
InnoDB data dictionary.
Table 19.12. INNODB_SYS_FOREIGN_COLS Columns
| Column name | Description |
|---|---|
ID | The foreign key index associated with this index key field, using the
same value as INNODB_SYS_FOREIGN.ID. |
FOR_COL_NAME | The name of the associated column in the child table. |
REF_COL_NAME | The name of the associated column in the parent table. |
POS | The ordinal position of this key field within the foreign key index, starting from 0. |
Notes:
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
You must have the PROCESS privilege to
query this table.
The INNODB_SYS_TABLESTATS view provides status
information about performance statistics for
InnoDB tables. These statistics represent
low-level information used by the MySQL optimizer to calculate
which index to use when querying an InnoDB
table. This information is derived from in-memory data structures
rather than corresponding to data stored on disk.
InnoDB tables are represented in this view if
they have been opened since the last server restart, and not aged
out of the table cache. Tables for which persistent stats are
available are always represented in this view.
Table 19.13. INNODB_SYS_TABLESTATS Columns
| Column name | Description |
|---|---|
TABLE_ID | An identifier representing the table for which statistics are available,
using the same value as
INNODB_SYS_TABLES.TABLE_ID. |
NAME | The name of the table, using the same value as
INNODB_SYS_TABLES.NAME. |
STATS_INITIALIZED | The value is Initialized if the statistics are
already collected, Uninitialized if
not. |
NUM_ROWS | The current estimated number of rows in the table. Updated after each DML operation. Could be imprecise if uncommitted transactions are inserting into or deleting from the table. |
CLUST_INDEX_SIZE | Number of pages on disk that store the clustered index, which holds the
InnoDB table data in primary key order.
This value might be null if no statistics are collected
yet for the table. |
OTHER_INDEX_SIZE | Number of pages on disk that store all secondary indexes for the table. This value might be null if no statistics are collected yet for the table. |
MODIFIED_COUNTER | The number of rows modified by DML operations, such as
INSERT, UPDATE,
DELETE, and also cascade operations
from foreign keys. |
AUTOINC | |
REF_COUNT | When this counter reaches zero, the table metadata can be evicted from the table cache. |
Notes:
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
You must have the PROCESS privilege to
query this table.
The INNODB_BUFFER_PAGE table holds information
about each page in the
InnoDB buffer
pool.
Table 19.14. INNODB_BUFFER_PAGE Columns
| Column name | Description |
|---|---|
POOL_ID | Buffer Pool ID. An identifier to distinguish between multiple buffer pool instances. |
BLOCK_ID | Buffer Pool Block ID. |
SPACE | Tablespace ID. Uses the same value as in
INNODB_SYS_TABLES.SPACE. |
PAGE_NUMBER | Page number. |
PAGE_TYPE | Page type string. One of allocated (Freshly allocated
page), index (B-tree node),
undo_log (Undo log page),
inode (Index node),
ibuf_free_list (Insert buffer free
list), ibuf_bitmap (Insert buffer
bitmap), system (System page),
trx_system (Transaction system data),
file_space_header (File space header),
extent_descriptor (Extent descriptor
page), blob (Uncompressed BLOB page),
compressed_blob (First compressed BLOB
page), compressed_blob2 (Subsequent
comp BLOB page), unknown (unknown). |
FLUSH_TYPE | Flush type. |
FIX_COUNT | Number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted. |
IS_HASHED | Whether hash index has been built on this page. |
NEWEST_MODIFICATION | Log Sequence Number of the youngest modification. |
OLDEST_MODIFICATION | Log Sequence Number of the oldest modification. |
ACCESS_TIME | Time of first access. |
TABLE_NAME | Name of the table the page belongs to. |
INDEX_NAME | Name of the index the page belongs to. It can be the name of a clustered index or a secondary index. |
NUMBER_RECORDS | Number of records within the page. |
DATA_SIZE | Sum of the sizes of the records. |
COMPRESSED_SIZE | Compressed page size. Null for pages that are not compressed. |
PAGE_STATE | Page state. A page with valid data has one of the following states:
FILE_PAGE (buffers a page of data from
a file), MEMORY (buffers a page from an
in-memory object),
Other possible states (managed by
InnoDB) are: null,
READY_FOR_USE,
NOT_USED,
REMOVE_HASH. |
IO_FIX | Specifies whether any I/O is pending for this page:
IO_NONE = no pending I/O,
IO_READ = read pending,
IO_WRITE = write pending. |
IS_OLD | bpage->old. |
FREE_PAGE_CLOCK | bpage->freed_page_clock. |
Notes:
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
You must have the PROCESS privilege to
query this table.
The INNODB_BUFFER_PAGE_LRU table holds
information about the pages in the InnoDB
buffer pool, in particular how they are ordered in the LRU list
that determines which pages to evict from the buffer pool when it
becomes full.
The definition for this page is the same as for
INNODB_BUFFER_PAGE,
except this table has an LRU_POSITION column
instead of BLOCK_ID.
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
The INNODB_BUFFER_POOL_STATS table represents
the same kinds of InnoDB buffer pool
information as the output from SHOW ENGINE INNODB
STATUS.
The idea of making pages in the buffer pool “young” or “not young” refers to transferring them between the sublists at the head and tail of the buffer pool data structure. Pages made “young” take longer to age out of the buffer pool, while pages made “not young” are moved much closer to the point of eviction.
Column meanings in this table are intended to be self-explanatory
for developers who formerly parsed the
SHOW ENGINE INNODB
STATUS to get these values.
Table 19.15. INNODB_BUFFER_POOL_STATS Columns
| Column name | Description |
|---|---|
POOL_ID | |
POOL_SIZE | |
FREE_BUFFERS | |
DATABASE_PAGES | |
OLD_DATABASE_PAGES | |
MODIFIED_DATABASE_PAGES | |
PENDING_DECOMPRESS | |
PENDING_READS | |
PENDING_FLUSH_LRU | |
PENDING_FLUSH_LIST | |
PAGES_MADE_YOUNG | |
PAGES_NOT_MADE_YOUNG | |
PAGES_MADE_YOUNG_RATE | |
PAGES_MADE_NOT_YOUNG_RATE | |
NUMBER_PAGES_READ | |
NUMBER_PAGES_CREATED | |
NUMBER_PAGES_WRITTEN | |
PAGES_READ_RATE | |
PAGES_CREATE_RATE | |
PAGES_WRITTEN_RATE | |
NUMBER_PAGES_GET | |
HIT_RATE | |
YOUNG_MAKE_PER_THOUSAND_GETS | |
NOT_YOUNG_MAKE_PER_THOUSAND_GETS | |
NUMBER_PAGES_READ_AHEAD | |
NUMBER_READ_AHEAD_EVICTED | |
READ_AHEAD_RATE | |
REAT_AHEAD_EVICTED_RATE | |
LRU_IO_TOTAL | |
LRU_IO_CURRENT | |
UNCOMPRESS_TOTAL | |
UNCOMPRESS_CURRENT |
Notes:
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Since the INFORMATION_SCHEMA is a
general-purpose way to monitor the MySQL server, use this
table rather than the corresponding InnoDB
system table for any new monitoring application you develop.
You must have the PROCESS privilege to
query this table.
This INFORMATION_SCHEMA table presents a wide
variety of InnoDB performance information,
complementing the specific focus areas of the
PERFORMANCE_SCHEMA tables for
InnoDB. With simple queries, you can check the
overall health of the system. With more detailed queries, you can
diagnose issues such as performance bottlenecks, resource
shortages, and application issues.
Each monitor represents a point within the
InnoDB source code that is instrumented to
gather counter information. Each counter can be started, stopped,
and reset. You can also perform these actions for a group of
counters using their common module name.
Table 19.16. INNODB_METRICS Columns
| Column name | Description |
|---|---|
NAME | Unique name for the counter. |
SUBSYSTEM | The aspect of InnoDB that the metric applies to. See
the list following the table for the corresponding module
names to use with the SET GLOBAL
syntax. |
COUNT | Value since the counter is enabled. |
MAX_COUNT | Maximum value since the counter is enabled. |
MIN_COUNT | Minimum value since the counter is enabled. |
AVG_COUNT | Average value since the counter is enabled. |
COUNT_RESET | Counter value since it was last reset. (The _RESET
fields act like the lap counter on a stopwatch: you can
measure the activity during some time interval, while the
cumulative figures are still available in the
COUNT, MAX_COUNT,
and so on fields.) |
MAX_COUNT_RESET | Maximum counter value since it was last reset. |
MIN_COUNT_RESET | Minimum counter value since it was last reset. |
AVG_COUNT_RESET | Average counter value since it was last reset. |
TIME_ENABLED | Timestamp of last start. |
TIME_DISABLED | Timestamp of last stop. |
TIME_ELAPSED | Elapsed time in seconds since the counter started. |
TIME_RESET | Timestamp of last stop. |
STATUS | Whether the counter is still running ( |
TYPE | Whether the item is a cumulative counter, or measures the current value of some resource. |
COMMENT | Additional description. |
Notes:
You must have the PROCESS privilege to
query this table.
By default, relatively little data is collected. To start,
stop, and reset counters, you set one of the the configuration
options
innodb_monitor_enable,
innodb_monitor_disable,
innodb_monitor_reset, or
innodb_monitor_reset_all,
using the name of the counter, the name of the module, a
wildcard match for such a name using the “%”
character, or the special keyword all. For
example:
-- Turn on a counter: set global innodb_monitor_enable =counter_name; -- Turn off a counter: set global innodb_monitor_disable =counter_name; -- Reset a counter: set global innodb_monitor_reset =counter_name; -- Reset all the counter values: set global innodb_monitor_reset_all =counter_name; -- Turn on a group of counters: set global innodb_monitor_enable =module_name; -- Turn off a group of counters: set global innodb_monitor_enable =module_name; -- Turn on monitor "dml_num_inserts": set global innodb_monitor_enable = dml_num_inserts; -- Reset all values for "dml_num_inserts": set global innodb_monitor_reset_all = dml_num_inserts; -- Reset the counters for all dml monitors set global innodb_monitor_reset = "dml_%"; -- Turn on all monitors for the transaction subsystem set global innodb_monitor_enable = module_trx; -- Turn off all monitors set global innodb_monitor_disable = all;
The module names correspond to, but are not identical to, the
values from the SUBSYSTEM column. Here are
the values you can use for
module_name with the
innodb_monitor_enable and
related configuration options, along with the corresponding
SUBSYSTEM names:
module_metadata (subsystem =
metadata)
module_lock (subsystem =
lock)
module_buffer (subsystem =
buffer)
module_buf_page (subsystem =
buffer_page_io)
module_os (subsystem =
os)
module_trx (subsystem =
transaction)
module_purge (subsystem =
purge)
module_compress (subsystem =
compression)
module_file (subsystem =
file_system)
module_index (subsystem =
index)
module_adaptive_hash (subsystem =
adaptive_hash_index)
module_ibuf_system (subsystem =
change_buffer)
module_srv (subsystem =
server)
module_ddl (subsystem =
ddl)
module_dml (subsystem =
dml)
module_log (subsystem =
recovery)
module_icp (subsystem =
icp)
Because each counter imposes some degree of runtime overhead on the server, typically you enable more counters on test and development servers during experimentation and benchmarking, and only enable counters on production servers to diagnose known issues or monitor aspects that are likely to be bottlenecks for a particular server and workload.
The items represented in the innodb_metrics
table are subject to change, so for the most up-to-date list,
query a running MySQL server. As of MySQL 5.6.4, the items
include:
mysql (information_schema) > select name, subsystem from innodb_metrics order by name; +------------------------------------------+---------------------+ | name | subsystem | +------------------------------------------+---------------------+ | adaptive_hash_pages_added | adaptive_hash_index | | adaptive_hash_pages_removed | adaptive_hash_index | | adaptive_hash_rows_added | adaptive_hash_index | | adaptive_hash_rows_deleted_no_hash_entry | adaptive_hash_index | | adaptive_hash_rows_removed | adaptive_hash_index | | adaptive_hash_rows_updated | adaptive_hash_index | | adaptive_hash_searches | adaptive_hash_index | | adaptive_hash_searches_btree | adaptive_hash_index | | buffer_data_reads | buffer | | buffer_data_written | buffer | | buffer_flush_adaptive | buffer | | buffer_flush_adaptive_pages | buffer | | buffer_flush_adaptive_total_pages | buffer | | buffer_flush_async | buffer | | buffer_flush_async_pages | buffer | | buffer_flush_async_total_pages | buffer | | buffer_flush_background | buffer | | buffer_flush_background_pages | buffer | | buffer_flush_background_total_pages | buffer | | buffer_flush_batches | buffer | | buffer_flush_batch_num_scan | buffer | | buffer_flush_batch_pages | buffer | | buffer_flush_batch_scanned | buffer | | buffer_flush_batch_scanned_per_call | buffer | | buffer_flush_batch_total_pages | buffer | | buffer_flush_max_dirty | buffer | | buffer_flush_max_dirty_pages | buffer | | buffer_flush_max_dirty_total_pages | buffer | | buffer_flush_neighbor | buffer | | buffer_flush_neighbor_pages | buffer | | buffer_flush_neighbor_total_pages | buffer | | buffer_flush_sync | buffer | | buffer_flush_sync_pages | buffer | | buffer_flush_sync_total_pages | buffer | | buffer_LRU_batches | buffer | | buffer_LRU_batch_num_scan | buffer | | buffer_LRU_batch_pages | buffer | | buffer_LRU_batch_scanned | buffer | | buffer_LRU_batch_scanned_per_call | buffer | | buffer_LRU_batch_total_pages | buffer | | buffer_LRU_get_free_search | Buffer | | buffer_LRU_search_num_scan | buffer | | buffer_LRU_search_scanned | buffer | | buffer_LRU_search_scanned_per_call | buffer | | buffer_LRU_single_flush_failure_count | Buffer | | buffer_LRU_single_flush_num_scan | buffer | | buffer_LRU_single_flush_scanned | buffer | | buffer_LRU_single_flush_scanned_per_call | buffer | | buffer_LRU_unzip_search_num_scan | buffer | | buffer_LRU_unzip_search_scanned | buffer | | buffer_LRU_unzip_search_scanned_per_call | buffer | | buffer_pages_created | buffer | | buffer_pages_read | buffer | | buffer_pages_written | buffer | | buffer_page_read_blob | buffer_page_io | | buffer_page_read_fsp_hdr | buffer_page_io | | buffer_page_read_ibuf_bitmap | buffer_page_io | | buffer_page_read_ibuf_free_list | buffer_page_io | | buffer_page_read_index_ibuf_leaf | buffer_page_io | | buffer_page_read_index_ibuf_non_leaf | buffer_page_io | | buffer_page_read_index_inode | buffer_page_io | | buffer_page_read_index_leaf | buffer_page_io | | buffer_page_read_index_non_leaf | buffer_page_io | | buffer_page_read_other | buffer_page_io | | buffer_page_read_system_page | buffer_page_io | | buffer_page_read_trx_system | buffer_page_io | | buffer_page_read_undo_log | buffer_page_io | | buffer_page_read_xdes | buffer_page_io | | buffer_page_read_zblob | buffer_page_io | | buffer_page_read_zblob2 | buffer_page_io | | buffer_page_written_blob | buffer_page_io | | buffer_page_written_fsp_hdr | buffer_page_io | | buffer_page_written_ibuf_bitmap | buffer_page_io | | buffer_page_written_ibuf_free_list | buffer_page_io | | buffer_page_written_index_ibuf_leaf | buffer_page_io | | buffer_page_written_index_ibuf_non_leaf | buffer_page_io | | buffer_page_written_index_inode | buffer_page_io | | buffer_page_written_index_leaf | buffer_page_io | | buffer_page_written_index_non_leaf | buffer_page_io | | buffer_page_written_other | buffer_page_io | | buffer_page_written_system_page | buffer_page_io | | buffer_page_written_trx_system | buffer_page_io | | buffer_page_written_undo_log | buffer_page_io | | buffer_page_written_xdes | buffer_page_io | | buffer_page_written_zblob | buffer_page_io | | buffer_page_written_zblob2 | buffer_page_io | | buffer_pool_pages_data | buffer | | buffer_pool_pages_dirty | buffer | | buffer_pool_pages_free | buffer | | buffer_pool_pages_in_flush | buffer | | buffer_pool_pages_misc | buffer | | buffer_pool_pages_total | buffer | | buffer_pool_reads | buffer | | buffer_pool_read_ahead | buffer | | buffer_pool_read_ahead_evicted | buffer | | buffer_pool_read_requests | buffer | | buffer_pool_size | server | | buffer_pool_wait_free | buffer | | buffer_pool_write_requests | buffer | | compress_pages_compressed | compression | | compress_pages_decompressed | compression | | ddl_background_drop_tables | ddl | | dml_deletes | dml | | dml_inserts | dml | | dml_reads | dml | | dml_updates | dml | | file_num_open_files | file_system | | ibuf_merges | change_buffer | | ibuf_merges_delete | change_buffer | | ibuf_merges_delete_mark | change_buffer | | ibuf_merges_discard_delete | change_buffer | | ibuf_merges_discard_delete_mark | change_buffer | | ibuf_merges_discard_insert | change_buffer | | ibuf_merges_insert | change_buffer | | ibuf_size | change_buffer | | icp_attempts | icp | | icp_match | icp | | icp_no_match | icp | | icp_out_of_range | icp | | index_merges | index | | index_splits | index | | innodb_activity_count | server | | innodb_background_drop_table_usec | server | | innodb_checkpoint_usec | server | | innodb_dblwr_pages_written | server | | innodb_dblwr_writes | server | | innodb_dict_lru_usec | server | | innodb_ibuf_merge_usec | server | | innodb_log_flush_usec | server | | innodb_master_active_loops | server | | innodb_master_idle_loops | server | | innodb_master_purge_usec | server | | innodb_master_thread_sleeps | server | | innodb_mem_validate_usec | server | | innodb_page_size | server | | innodb_rwlock_s_os_waits | server | | innodb_rwlock_s_spin_rounds | server | | innodb_rwlock_s_spin_waits | server | | innodb_rwlock_x_os_waits | server | | innodb_rwlock_x_spin_rounds | server | | innodb_rwlock_x_spin_waits | server | | lock_deadlocks | lock | | lock_rec_locks | lock | | lock_rec_lock_created | lock | | lock_rec_lock_removed | lock | | lock_rec_lock_requests | lock | | lock_rec_lock_waits | lock | | lock_row_lock_current_waits | lock | | lock_row_lock_time | lock | | lock_row_lock_time_avg | lock | | lock_row_lock_time_max | lock | | lock_row_lock_waits | lock | | lock_table_locks | lock | | lock_table_lock_created | lock | | lock_table_lock_removed | lock | | lock_table_lock_waits | lock | | lock_timeouts | lock | | log_checkpoints | recovery | | log_lsn_buf_pool_oldest | recovery | | log_lsn_checkpoint_age | recovery | | log_lsn_current | recovery | | log_lsn_last_checkpoint | recovery | | log_lsn_last_flush | recovery | | log_max_modified_age_async | recovery | | log_max_modified_age_sync | recovery | | log_num_log_io | recovery | | log_pending_checkpoint_writes | recovery | | log_pending_log_writes | recovery | | log_waits | recovery | | log_writes | recovery | | log_write_requests | recovery | | metadata_mem_pool_size | metadata | | metadata_table_handles_closed | metadata | | metadata_table_handles_opened | metadata | | metadata_table_reference_count | metadata | | os_data_fsyncs | os | | os_data_reads | os | | os_data_writes | os | | os_log_bytes_written | os | | os_log_fsyncs | os | | os_log_pending_fsyncs | os | | os_log_pending_writes | os | | os_pending_reads | os | | os_pending_writes | os | | purge_del_mark_records | purge | | purge_dml_delay_usec | purge | | purge_invoked | purge | | purge_undo_log_pages | purge | | purge_upd_exist_or_extern_records | purge | | trx_active_transactions | transaction | | trx_commits_insert_update | transaction | | trx_nl_ro_commits | transaction | | trx_rollbacks | transaction | | trx_rollbacks_savepoint | transaction | | trx_rollback_active | transaction | | trx_ro_commits | transaction | | trx_rseg_curent_size | transaction | | trx_rseg_history_len | transaction | | trx_rw_commits | transaction | | trx_undo_slots_cached | transaction | | trx_undo_slots_used | transaction | +------------------------------------------+---------------------+ 201 rows in set (0.00 sec)
The INNODB_FT_CONFIG table displays metadata
about the FULLTEXT index and associated
processing for an InnoDB table.
This table is only accessible to users with the
SUPER privilege. Before you query this table,
set the configuration variable
innodb_ft_aux_table to the name
(including the database name) of the table that contains the
FULLTEXT index, for example
test/articles.
Table 19.17. INNODB_FT_CONFIG Columns
| Column name | Description |
|---|---|
KEY | The name designating an item of metadata for an
InnoDB table containing a
FULLTEXT index. |
VALUE | The value associated with the corresponding KEY
column, reflecting some limit or current value for an
aspect of a FULLTEXT index for an
InnoDB table. |
Notes:
This table initially appears empty, until you set the value of
the configuration variable
innodb_ft_aux_table.
You must have the PROCESS privilege to
query this table.
The values for the KEY column might evolve
depending on the needs for performance tuning and debugging
for InnoDB full-text processing. Currently,
the key values include
optimize_checkpoint_limit,
synced_doc_id,
last_optimized_word,
deleted_doc_count,
total_word_count,
optimize_start_time,
optimize_end_time,
stopword_table_name,
use_stopword, and
table_state.
For more information, see Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_DEFAULT_STOPWORD table holds a
list of stopwords that are
used by default when creating a FULLTEXT index
on an InnoDB table.
Table 19.18. INNODB_FT_DEFAULT_STOPWORD Columns
| Column name | Description |
|---|---|
value | A word that is used by default as a stopword for
FULLTEXT indexes on
InnoDB tables. Not used if you override
the default stopword processing with either the
innodb_ft_server_stopword_table
or the
innodb_ft_user_stopword_table
option. |
Notes:
You must have the PROCESS privilege to
query this table.
For more information, see Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_INDEX_TABLE table displays
information about the inverted index used to process text searches
against the FULLTEXT index of an
InnoDB table.
This table is only accessible to users with the
SUPER privilege. Before you query this table,
set the configuration variable
innodb_ft_aux_table to the name
(including the database name) of the table that contains the
FULLTEXT index, for example
test/articles.
Table 19.19. INNODB_FT_INDEX_TABLE Columns
| Column name | Description |
|---|---|
WORD | A word extracted from the text of the columns that are part of a
FULLTEXT. |
FIRST_DOC_ID | The first document ID that this word appears in in the
FULLTEXT index. |
LAST_DOC_ID | The last document ID that this word appears in in the
FULLTEXT index. |
DOC_COUNT | The number of rows this word appears in in the
FULLTEXT index. The same word can occur
several times within the cache table, once for each
combination of DOC_ID and
POSITION values. |
DOC_ID | The document ID of the row containing the word. This value might reflect
the value of an ID column that you defined for the
underlying table, or it can be a sequence value generated
by InnoDB when the table does not
contain a suitable column. |
POSITION | The position of this particular instance of the word within the relevant
document identified by the DOC_ID
value. |
Notes:
This table initially appears empty, until you set the value of
the configuration variable
innodb_ft_aux_table.
You must have the PROCESS privilege to
query this table.
For more information, see Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_INDEX_CACHE table displays token
information about newly inserted rows in a
FULLTEXT index for an InnoDB
table. To avoid expensive index reorganization during DML
operations for an InnoDB
FULLTEXT index, the information about newly
indexed words is stored separately, and combined with the main
search index only when you issue the OPTIMIZE
TABLE statement for the InnoDB table.
This table is only accessible to users with the
SUPER privilege. Before you query this table,
set the configuration variable
innodb_ft_aux_table to the name
(including the database name) of the table that contains the
FULLTEXT index, for example
test/articles.
Table 19.20. INNODB_FT_INDEX_CACHE Columns
| Column name | Description |
|---|---|
WORD | A word extracted from the text of a newly inserted row. |
FIRST_DOC_ID | The first document ID that this word appears in in the
FULLTEXT index. |
LAST_DOC_ID | The last document ID that this word appears in in the
FULLTEXT index. |
DOC_COUNT | The number of rows this word appears in in the
FULLTEXT index. The same word can occur
several times within the cache table, once for each
combination of DOC_ID and
POSITION values. |
DOC_ID | The document ID of the newly inserted row. This value might reflect the
value of an ID column that you defined for the underlying
table, or it can be a sequence value generated by
InnoDB when the table does not contain
a suitable column. |
POSITION | The position of this particular instance of the word within the relevant
document identified by the DOC_ID
value. The value does not represent an absolute position;
it is an offset added to the POSITION
of the previous instance of that word. |
Notes:
This table initially appears empty, until you set the value of
the configuration variable
innodb_ft_aux_table.
You must have the PROCESS privilege to
query this table.
For more information, see Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_INSERTED table contains a list of
IDs for newly inserted rows, for an InnoDB
table with a FULLTEXT index. These IDs refer to
corresponding values within the
innodb_ft_index_table and
innodb_ft_index_cache tables. (When
you issue an OPTIMIZE TABLE
statement for the InnoDB table containing the
FULLTEXT index, the IDs are transferred from
the cache table to the table that represents the index.)
This table is only accessible to users with the
SUPER privilege. Before you query this table,
set the configuration variable
innodb_ft_aux_table to the name
(including the database name) of the table that contains the
FULLTEXT index, for example
test/articles.
Table 19.21. INNODB_FT_INSERTED Columns
| Column name | Description |
|---|---|
DOC_ID | The document ID of the newly inserted row. This value might reflect the
value of an ID column that you defined for the underlying
table, or it can be a sequence value generated by
InnoDB when the table does not contain
a suitable column. This value is used to look up rows in
the innodb_ft_index_cache table, when
you do text searches before the data is merged into the
FULLTEXT index by an
OPTIMIZE TABLE statement. |
Notes:
This table initially appears empty, until you set the value of
the configuration variable
innodb_ft_aux_table.
You must have the PROCESS privilege to
query this table.
For more information, see Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_DELETED table records rows that
are deleted from the FULLTEXT index for an
InnoDB table. To avoid expensive index
reorganization during DML operations for an
InnoDB FULLTEXT index, the
information about newly deleted words is stored separately,
filtered out of search results when you do a text search, and
removed from the main search index only when you issue the
OPTIMIZE TABLE statement for the
InnoDB table.
This table is only accessible to users with the
SUPER privilege. Before you query this table,
set the configuration variable
innodb_ft_aux_table to the name
(including the database name) of the table that contains the
FULLTEXT index, for example
test/articles.
Table 19.22. INNODB_FT_DELETED Columns
| Column name | Description |
|---|---|
DOC_ID | The document ID of the newly deleted row. This value might reflect the
value of an ID column that you defined for the underlying
table, or it can be a sequence value generated by
InnoDB when the table does not contain
a suitable column. This value is used to skip rows in the
innodb_ft_index_table table, when you
do text searches before data for deleted rows is
physically removed from the FULLTEXT
index by an OPTIMIZE TABLE
statement. |
Notes:
This table initially appears empty, until you set the value of
the configuration variable
innodb_ft_aux_table.
You must have the PROCESS privilege to
query this table.
For more information, see Section 12.9, “Full-Text Search Functions”.
The INNODB_FT_BEING_DELETED table is a
temporary work table while document IDs in the
INNODB_FT_DELETED table are being
removed from an InnoDB
FULLTEXT index during an
OPTIMIZE TABLE operation. Because
its contents typically have a short lifetime, this table has
limited utility for monitoring or debugging.
This table is only accessible to users with the
SUPER privilege. This table initially appears
empty, until you set the value of the configuration variable
innodb_ft_aux_table.
Table 19.23. INNODB_FT_BEING_DELETED Columns
| Column name | Description |
|---|---|
DOC_ID | The document ID of the row that is in the process of being deleted. This
value might reflect the value of an ID column that you
defined for the underlying table, or it can be a sequence
value generated by InnoDB when the
table does not contain a suitable column. This value is
used to skip rows in the
innodb_ft_index_table table, when you
do text searches before data for deleted rows is
physically removed from the FULLTEXT
index by an OPTIMIZE TABLE
statement. |
Notes:
This table initially appears empty, until you set the value of
the configuration variable
innodb_ft_aux_table.
You must have the PROCESS privilege to
query this table.
For more information, see Section 12.9, “Full-Text Search Functions”.
Some extensions to SHOW statements
accompany the implementation of
INFORMATION_SCHEMA:
INFORMATION_SCHEMA is an information database,
so its name is included in the output from
SHOW DATABASES. Similarly,
SHOW TABLES can be used with
INFORMATION_SCHEMA to obtain a list of its
tables:
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
+---------------------------------------+
| Tables_in_INFORMATION_SCHEMA |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
27 rows in set (0.00 sec)
SHOW COLUMNS and
DESCRIBE can display information
about the columns in individual
INFORMATION_SCHEMA tables.
SHOW statements that accept a
LIKE clause to limit the rows
displayed also permit a WHERE clause that
specifies more general conditions that selected rows must satisfy:
SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW DATABASES SHOW FUNCTION STATUS SHOW INDEX SHOW OPEN TABLES SHOW PROCEDURE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW TRIGGERS SHOW VARIABLES
The WHERE clause, if present, is evaluated
against the column names displayed by the
SHOW statement. For example, the
SHOW CHARACTER SET statement
produces these output columns:
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
...
To use a WHERE clause with
SHOW CHARACTER SET, you would refer
to those column names. As an example, the following statement
displays information about character sets for which the default
collation contains the string 'japanese':
mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+
This statement displays the multi-byte character sets:
mysql> SHOW CHARACTER SET WHERE Maxlen > 1;
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+