MENU


Information Databases

MySQL ships with several built-in databases containing tables and views that can be queried to monitor the operation of the server and the databases it hosts. Since MySQL 8.0, a transactional data dictionary stores information about database objects; in earlier releases this metadata was scattered across .frm files, non-transactional tables, and storage-engine-specific dictionaries.

INFORMATION_SCHEMA

INFORMATION_SCHEMA contains a variety of tables that store information about different aspects of the server and its databases. A few of the most commonly queried tables:

SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'golden_shop';

Row/size statistics for every table in a schema.

Much of the information in INFORMATION_SCHEMA can also be retrieved with the SHOW statement; the two statements below are equivalent:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name' [AND table_name LIKE 'wild'];

SHOW TABLES FROM db_name [LIKE 'wild'];

Querying INFORMATION_SCHEMA directly versus using the shorthand SHOW statement.

Other notable tables include CHARACTER_SETS, COLLATIONS, ENGINES, KEY_COLUMN_USAGE, PARTITIONS, PLUGINS, PROCESSLIST, REFERENTIAL_CONSTRAINTS, ROUTINES, SCHEMATA, TABLE_CONSTRAINTS, TRIGGERS, VIEWS, and a family of INNODB_* tables (e.g. INNODB_BUFFER_PAGE, INNODB_METRICS, INNODB_TRX) exposing InnoDB-internal state.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%mail%'
ORDER BY TABLE_NAME;

Finds every column across every table whose name contains "mail".

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'User' AND TABLE_SCHEMA = 'db';

Lists all column names of a specific table.

PERFORMANCE_SCHEMA

PERFORMANCE_SCHEMA is a built-in schema that provides performance-related metrics and statistics for the server and its operations, allowing analysis and optimization of resource usage, query execution times, and other metrics. See also Performance Schema and Sys Schema.

SELECT DIGEST_TEXT, SUM(SUM_TIMER_WAIT) AS TOTAL_TIME
FROM PERFORMANCE_SCHEMA.events_statements_summary_by_digest
GROUP BY DIGEST_TEXT
ORDER BY TOTAL_TIME DESC
LIMIT 10;

The ten most time-consuming statement digests.

Other Performance Schema tables cover replication (replication_applier_status, replication_connection_configuration, replication_group_members), locking (metadata_locks, data_lock_waits, table_lock_waits_summary_by_table), memory (memory_summary_global_by_event_name), and threads/sessions (threads, processlist, session_status, session_variables).

SYS

The SYS schema contains views and functions that allow administrators and developers to retrieve information about database objects (tables, indexes, queries) and performance metrics (query execution times, resource usage), largely by presenting PERFORMANCE_SCHEMA and INFORMATION_SCHEMA data in a more human-readable form.

In addition to views, the sys schema includes stored procedures (e.g. diagnostics(), the ps_setup_* family for configuring Performance Schema instrumentation, ps_trace_thread()), and functions (e.g. format_bytes(), format_time(), version()).

SELECT table_name, rows_fetched, rows_inserted, rows_updated, rows_deleted
FROM SYS.schema_table_statistics
WHERE table_name = 'customers';

Row-access counters for one table.

MYSQL

The MYSQL database stores information about users, privileges, and other configuration details for the server itself. It is used to manage access control and authentication and to store global configuration settings. It contains system tables such as user, db, tables_priv, columns_priv, procs_priv, host, func, plugin, and global_grants, along with logging tables (general_log, slow_log), replication metadata (gtid_executed, slave_master_info), time zone tables, and more.

SELECT user, host, db
FROM mysql.db
WHERE db = 'my_database';

Which accounts have database-level grants on a given schema.

SHOW TABLES FROM mysql;

Lists every system table in the MYSQL database itself.


SHOW TABLES FROM mysql;

Tables_in_mysql
columns_priv
component
db
default_roles
engine_cost
func
general_log
global_grants
gtid_executed
help_category
help_keyword
help_relation
help_topic
innodb_index_stats
innodb_table_stats
ndb_binlog_index
password_history
plugin
procs_priv
proxies_priv
replication_asynchronous_connection_failover
replication_asynchronous_connection_failover_managed
replication_group_configuration_version
replication_group_member_actions
role_edges
server_cost
servers
slave_master_info
slave_relay_log_info
slave_worker_info
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
The MYSQL database should be treated with care, since it contains sensitive information about server configuration and user accounts. Modifying its tables directly can have serious consequences, such as locking yourself out of the server or inadvertently granting privileges to unauthorized users. As a best practice, use GRANT and REVOKE to manage user privileges rather than editing the MYSQL tables directly. 

-- Row/size statistics for every table in a schema
SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'golden_shop';

-- Equivalent forms: querying INFORMATION_SCHEMA directly vs. SHOW
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name';

SHOW TABLES FROM db_name;

-- Finding columns by name pattern across all tables
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%mail%'
ORDER BY TABLE_NAME;

-- The ten most time-consuming statement digests (PERFORMANCE_SCHEMA)
SELECT DIGEST_TEXT, SUM(SUM_TIMER_WAIT) AS TOTAL_TIME
FROM PERFORMANCE_SCHEMA.events_statements_summary_by_digest
GROUP BY DIGEST_TEXT
ORDER BY TOTAL_TIME DESC
LIMIT 10;

-- Overview of the views/functions in the SYS schema
USE sys;
SELECT * FROM schema_table_statistics;
SELECT version();

-- Table access counters (SYS schema)
SELECT table_name, rows_fetched, rows_inserted, rows_updated, rows_deleted
FROM SYS.schema_table_statistics
WHERE table_name = 'customers';

-- Which accounts have grants on a given database (MYSQL database)
SELECT user, host, db
FROM mysql.db
WHERE db = 'my_database';