Skip to content
Tutorials Center
My learning

Topics

Lesson 42 of 75 ยท Storage Engines and Internals

The Data Dictionary, information_schema and sys Schema

Let MySQL describe itself: the data dictionary, information_schema tables, performance_schema, sys views, finding large tables and unused indexes, SHOW CREATE TABLE.

A running MySQL server knows a great deal about itself: every table and column, every index, how big each table is, which indexes queries actually use, and what each connection is doing right now. All of it is exposed through ordinary SQL, in a few special schemas. Learning to query them turns guesswork ("which table is eating the disk?") into a ten-second question.

This lesson tours the four places to look: the data dictionary behind everything, information_schema for standard metadata, performance_schema for runtime statistics, and the sys schema, which wraps the other two in friendly views. These schemas only exist on a real MySQL server, so the MySQL statements are read-only. The live examples use SQLite's sqlite_master and pragma_* functions to rebuild small information_schema-style views, which teaches the same query habits.

Four places to ask

data dictionary The server's own record of its objects.
information_schema Standard, read-only metadata views.
performance_schema Live counters and timings.
sys Readable views on top of both.
1 / 2

The data dictionary

Before MySQL 8.0, table definitions lived in .frm files next to the data, and a crash during a schema change could leave the two out of step. Since 8.0 the metadata is stored in a transactional data dictionary: ordinary InnoDB tables in the mysql schema that you cannot read or write directly. CREATE TABLE and ALTER TABLE now update it atomically, so a statement either happened fully or not at all.

You are not meant to query those hidden tables. Instead you read the dictionary through information_schema, which presents it as standard views, and through SHOW statements.

information_schema

information_schema follows a standard shared with other databases, with MySQL additions. These are the tables you will use most:

Table One row per Use it to
schemata database list databases and default charsets
tables table or view find sizes, engines, row formats
columns column search by name or type
statistics index column list indexes and their cardinality
table_constraints, key_column_usage, referential_constraints constraint map foreign keys
views, routines, triggers, events object audit programmable objects
partitions partition inspect partitioned tables

Find the largest tables. This is probably the most useful single query in this lesson:

SQL
SELECT table_schema, table_name, engine,
       table_rows,
       ROUND(data_length  / 1024 / 1024, 1) AS data_mb,
       ROUND(index_length / 1024 / 1024, 1) AS index_mb,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY data_length + index_length DESC
LIMIT 10;

data_length is the size of the clustered index (the rows), and index_length is the size of all secondary indexes together. For InnoDB, table_rows is only an estimate taken from sampling, sometimes off by 40% or more. Use SELECT COUNT(*) when you need an exact number.

Search for columns by name, for example before renaming something:

SQL
SELECT table_name, column_name, column_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'shop' AND column_name LIKE '%email%'
ORDER BY table_name, ordinal_position;

List the indexes of a schema, one row per column of each index:

SQL
SELECT table_name, index_name, non_unique, seq_in_index, column_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'shop'
ORDER BY table_name, index_name, seq_in_index;

In MySQL 8.0 and later, information_schema statistics such as table_rows, data_length and cardinality are cached for up to a day (information_schema_stats_expiry, default 86400 seconds). A table you just filled may show old numbers. Run ANALYZE TABLE name; to refresh one table, or SET SESSION information_schema_stats_expiry = 0; to read fresh values in your session.

The same idea in SQLite

SQLite has no information_schema, but sqlite_master lists every object and the pragma_* table-valued functions describe columns, indexes and foreign keys. Joining them rebuilds the same kind of catalog queries. This example runs live:

SQL An information_schema stand-in

Press Run (or Ctrl + Enter) to see the result here.

Each query returns its own table. What matters is that metadata is just rows you can filter, join and sort.

SHOW statements and SHOW CREATE TABLE

For quick interactive use, SHOW is shorter than querying information_schema:

SQL
SHOW DATABASES;
SHOW TABLES FROM shop;
SHOW FULL COLUMNS FROM orders;
SHOW INDEX FROM orders;
SHOW TABLE STATUS FROM shop LIKE 'orders'\G
SHOW CREATE TABLE orders\G

SHOW CREATE TABLE deserves the most attention. It prints the exact CREATE TABLE statement that would rebuild the table, including column definitions, indexes, foreign keys, engine, charset and collation. Copy it before you write any ALTER TABLE ... MODIFY, because MODIFY needs the full current definition (see ALTER TABLE and Online DDL). SHOW CREATE VIEW, SHOW CREATE TRIGGER and SHOW CREATE PROCEDURE do the same for other objects. In SQLite the matching lookup is SELECT sql FROM sqlite_master WHERE name = 'orders';.

performance_schema

performance_schema is an in-memory set of tables that the server fills while it runs: statement timings, wait events, file and table I/O, locks, memory use and connection details. It is enabled by default, costs a few percent of CPU at most, and starts empty each time the server restarts. Three groups matter for beginners:

  • Statement digests: events_statements_summary_by_digest groups statements by their normalized text (literals replaced by ?) with counts and total, average and maximum latency. This is how you find the worst queries. See Finding Slow Queries.
  • Table and index I/O: table_io_waits_summary_by_table and table_io_waits_summary_by_index_usage count the reads and writes per table and per index.
  • Current activity and locks: threads, events_statements_current, and data_locks and data_lock_waits (8.0), which show who holds and who waits for InnoDB locks.

Raw performance_schema table names are long and its times are in picoseconds, which is why the sys schema exists.

The sys schema

sys is a set of views, functions and procedures shipped with MySQL (5.7 and later) that read performance_schema and information_schema and present them in readable units. Each view usually comes twice: a formatted version (2.35 s, 120.5 MiB) and an x$ version with raw numbers for scripts.

View Answers
sys.schema_table_statistics Which tables get the most reads, writes and I/O wait?
sys.schema_unused_indexes Which indexes have not been used since the server started?
sys.schema_redundant_indexes Which indexes are covered by a longer index?
sys.statement_analysis Which normalized statements cost the most time?
sys.innodb_lock_waits Which transaction is blocking which?
sys.schema_auto_increment_columns How close is each AUTO_INCREMENT column to overflowing?
sys.host_summary, sys.user_summary What does each client host or user cost?
SQL
-- Indexes that nothing has used since the last restart
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'shop';

-- Indexes made unnecessary by another index
SELECT table_name, redundant_index_name, dominant_index_name
FROM sys.schema_redundant_indexes
WHERE table_schema = 'shop';

-- Which AUTO_INCREMENT columns are running out of room?
SELECT table_schema, table_name, column_name, auto_increment_ratio
FROM sys.schema_auto_increment_columns
ORDER BY auto_increment_ratio DESC
LIMIT 5;

"Unused" means unused since the server last restarted, and performance_schema counters reset then. An index that serves a monthly report looks unused after three weeks of uptime. Check over a full business cycle, never right after a restart, and make the index invisible first (ALTER TABLE t ALTER INDEX idx INVISIBLE;) so you can bring it back instantly if something slows down. Primary keys and unique constraints are never safe to drop just because they are unread: they enforce rules.

Finding redundant indexes live

An index on (customer_id) is redundant when another index starts with customer_id, such as (customer_id, placed_at), because the longer index can answer the same lookups. sys.schema_redundant_indexes finds these on MySQL. The same logic can be run on SQLite with a helper view. It also shows how to pivot index metadata into one row per index:

SQL Find redundant indexes

Press Run (or Ctrl + Enter) to see the result here.

The final result names idx_customer as covered by idx_customer_placed. Dropping it saves disk and speeds up writes, while lookups by customer_id keep working through the longer index (more on that rule in Designing Composite Indexes). idx_status is not flagged because no other index starts with status.

Privileges and cost

All of these schemas obey normal privileges: a user sees information_schema rows only for objects they can access, and performance_schema and sys usually need extra grants (SELECT on performance_schema.* and sys.*). Queries on information_schema.tables and statistics can be slow on servers with hundreds of thousands of tables, so filter by table_schema and table_name and avoid running them every second from monitoring.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which column pair in information_schema.tables gives the size of the clustered index and of all secondary indexes?

    data_length is the size of the rows (the clustered index) and index_length is the total size of secondary indexes. table_rows is only an estimate for InnoDB.
  2. 2. You loaded a million rows and information_schema.tables still shows old numbers. Why?

    information_schema_stats_expiry defaults to 86400 seconds. ANALYZE TABLE refreshes one table, and setting the variable to 0 reads live values in your session.
  3. 3. sys.schema_unused_indexes lists an index. What should you do first?

    The counters only cover the time since the last restart. An invisible index can be restored instantly if a rare query needs it.
  4. 4. What is SHOW CREATE TABLE most useful for before a MODIFY COLUMN statement?

    MODIFY replaces the whole column definition, so you copy the existing definition from SHOW CREATE TABLE and change only what you intend to.

Summary

  • MySQL 8.0 keeps its metadata in a transactional data dictionary, so DDL is atomic. You read it through information_schema and SHOW.
  • information_schema.tables, columns and statistics find sizes, columns and indexes. InnoDB row counts are estimates, and statistics are cached for up to a day.
  • performance_schema collects live statistics (statement digests, I/O, locks) and resets at restart.
  • sys wraps both in readable views: schema_unused_indexes, schema_redundant_indexes, statement_analysis, innodb_lock_waits.
  • Confirm "unused" over a full business cycle and use invisible indexes before dropping.
  • SQLite's sqlite_master and pragma_* functions offer the same kind of catalog queries.

Next: How MySQL Indexes Work, where we look inside the structure these views describe.