How is INFORMATION_SCHEMA implemented in MySQL?

The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL?

When I connect to a fresh installation I see two databases: mysql and information_schema. After using SHOW CREATE TABLE statements on the information_schema database, it looks like it's not implemented as a set of views but with base tables instead. Is this assumption correct? Or there are other system tables that are hidden from the user?

The INFORMATION_SCHEMA database is made up of temporary tables using the MEMORY storage engine.

Example: Here is the table INFORMATION_SCHEMA.TABLES in MySQL 5.5.12 (Windows Version)

mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

There is no physical folder for those tables, not even .frm files. You cannot mysqldump it. You cannot drop it. You cannot add tables to it. You cannot drop tables from it. So, where are the tables ???

All tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. They are totally internal to MySQL, so the .frm mechanisms are handled in mysqld. In my answer, I first showed the table layout of INFORMATION_SCHEMA.TABLES. It is a temporary table in memory. It is manipulated using storage engine protocols. Thus, when mysqld is shutdown, all information_schema tables are dropped. When mysqld is started, all information_schema tables are created as TEMPORARY tables and repopulated with metadata for every table in the mysql instance.

The INFORMATION_SCHEMA database was first introduced in MySQL 5.0 to give you access to metadata about tables of other storage engines. For example, you could do SHOW DATABASES to get a list of databases. You could also query for them like this:

SELECT schema_name database FROM information_schema.schemata;

You could retrieve table names in a database in two ways:

use mydb
show tables;

or

SELECT table_name from information_schema.tables WHERE table_schema = 'mydb';

Since its inception, MySQL has expanded the INFORMATION_SCHEMA database to have the processlist (as of MySQL 5.1). You can actually query the processlist looking for long running queries that are still running at least 10 minutes:

SELECT * FROM information_schema.processlist WHERE time >= 600\G

You can use the INFORMATION_SCHEMA to do every elaborate things: such as :

Get counts of all tables using specific storage engines:

SELECT COUNT(1) TableCount,IFNULL(engine,'Total') StorageEngine
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
AND engine IS NOT NULL
GROUP BY engine WITH ROLLUP;

Get the recommended MyISAM Key Buffer Size in MB

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw3,0,pw))+1,1)) recommended_key_buffer_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,
(SELECT 2 pw) B;

Get the recommended InnoDB Buffer Pool Size in GB

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw3,0,pw))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 3 pw) B;

Get Disk Usage of all Databases By Storage Engine in MB

```
SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize
FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_leng

StackExchange Database Administrators Q#3335, answer score: 32