The information_schema database

The information_schema database (often called I_S for brevity) is a virtual database that contains informative tables. These tables can be divided into several groups:

• Metadata tables: Tables such as SCHEMATA, TABLES, and COLUMNS contain information about the structure of databases, tables, columns, and so on.
• Status and variables tables: The GLOBAL_VARIABLES and SESSION_VARIABLES tables list the values of the server's system variables. The GLOBAL_STATUS and SESSION_STATUS tables provide information about the operations performed by the server.
• Privilege tables: The tables whose names end with _PRIVILEGES indicate users that have various permissions on objects.
• The PROFILING table: This table can be used to monitor the queries executed during the current session, and see which low-level operations are performed by the server.
• The PROCESSLIST table: This table shows the active sessions and their status.

Several tables provide information about InnoDB. Some of them are XtraDB-specific. These table names begin with INNODB_ or XTRADB_ if they only exist for XtraDB, discussed as follows:

• InnoDB locks tables: The INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX tables contain information about active locks, waits, and transactions that acquired a lock or are waiting for a lock, respectively.
• InnoDB buffer pool tables: Tables whose names start with INNODB_BUFFER_ are the buffer pool contents and page usage.
• The INNODB_METRICS table: This table provides information about some low-level operations performed by InnoDB.
• InnoDB compression tables: Tables whose names start with INNODB_CMP provide information about the performance of compressed pages.
• InnoDB full-text tables: Tables whose names start with INNODB_FT_ provide information about full-text indexes in InnoDB tables.
• InnoDB data dictionary tables: Tables whose names start with INNODB_SYS_ provide metadata about InnoDB tables, columns, and foreign keys. They are similar to the more generic tables that contain metadata, but these tables are specific to InnoDB. They also contain statistics and information about files.

Generally, the information that can be read from information_schema can also be obtained with the SHOW statements and vice versa. Querying information_schema is a more flexible and standard way to retrieve such information, but is also more verbose.

Information on the InnoDB activities can also be obtained in a human-readable form via the SHOW ENGINE InnoDB STATUS and SHOW ENGINE InnoDB MUTEX statements.

To answer the queries of information_schema, the server opens and reads the database files, which can be a slow operation. For this reason, the queries that are often executed on a production server should be optimized to only read the necessary files. This can usually be done with a good WHERE clause.