Applications that monitor databases may make frequent use of INFORMATION_SCHEMA tables. To write queries for these tables most efficiently, use the following general guidelines:

These INFORMATION_SCHEMA tables are implemented as views on data dictionary tables, so queries on them retrieve information from the data dictionary:

CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
KEY_COLUMN_USAGE
SCHEMATA
STATISTICS
TABLES
TABLE_CONSTRAINTS
VIEWS

Some types of values, even for non-view INFORMATION_SCHEMA tables, are retrieved by lookups from the data dictionary. This includes values such as database and table names, table types, and storage engines.

Some INFORMATION_SCHEMA tables contain columns that provide table statistics, such as CARDINALITY in STATISTICS and DATA_LENGTH, INDEX_LENGTH, and AUTO_INCREMENT in TABLES.

The server has two sources from which to retrieve table statistics. The STATISTICS and TABLES tables contain cached statistics, whereas the STATISTICS_DYNAMIC and TABLES_DYNAMIC tables contain the latest statistics, obtained directly from storage engines.

To write queries that retrieve table statistics, select from STATISTICS and TABLES and set the information_schema_stats system variable to control which statistics source the server uses:

To determine how to set information_schema_stats, consider the tradeoffs:

Set the global information_schema_stats value to determine the default used initially by all sessions. Individual sessions can set the session information_schema_stats value to override the global value as desired.

For INFORMATION_SCHEMA tables implemented as views on data dictionary tables, indexes on the underlying data dictionary tables permit the optimizer to construct efficient query execution plans. To see the choices made by the optimizer, use EXPLAIN. To also see the query used by the server to execute an INFORMATION_SCHEMA query, use SHOW WARNINGS immediately following EXPLAIN.

Consider this statement, which identifies collations for the utf8 character set:

mysql> SELECT COLLATION_NAME
    -> FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
    -> WHERE CHARACTER_SET_NAME = 'utf8';
+--------------------------+
| COLLATION_NAME           |
+--------------------------+
| utf8_general_ci          |
| utf8_tolower_ci          |
| utf8_bin                 |
| utf8_unicode_ci          |
| utf8_icelandic_ci        |
| utf8_latvian_ci          |
| utf8_romanian_ci         |
...

How does the server process that statement? To find out, use EXPLAIN:

mysql> EXPLAIN SELECT COLLATION_NAME
    -> FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
    -> WHERE CHARACTER_SET_NAME = 'utf8'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cs
   partitions: NULL
         type: const
possible_keys: PRIMARY,name
          key: name
      key_len: 194
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: col
   partitions: NULL
         type: ref
possible_keys: character_set_id
          key: character_set_id
      key_len: 8
          ref: const
         rows: 28
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.01 sec)

To see the query used to statisfy that statement, use SHOW WARNINGS:

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `col`.`name` AS `COLLATION_NAME`
         from `mysql`.`character_sets` `cs`
         join `mysql`.`collations` `col`
         where ((`col`.`character_set_id` = '33')
         and ('utf8' = 'utf8'))

As indicated by SHOW WARNINGS, the server handles the query on COLLATION_CHARACTER_SET_APPLICABILITY. as a query on the character_sets and collations data dictionary tables from the mysql system database.