20.20. The `INFORMATION_SCHEMA TABLES` Table

The TABLES table provides information about tables in databases.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`	`Table_`...
`TABLE_NAME`	`Table_`...
`TABLE_TYPE`
`ENGINE`	`Engine`	MySQL extension
`VERSION`	`Version`	The version number of the table's `.frm` file, MySQL extension
`ROW_FORMAT`	`Row_format`	MySQL extension
`TABLE_ROWS`	`Rows`	MySQL extension
`AVG_ROW_LENGTH`	`Avg_row_length`	MySQL extension
`DATA_LENGTH`	`Data_length`	MySQL extension
`MAX_DATA_LENGTH`	`Max_data_length`	MySQL extension
`INDEX_LENGTH`	`Index_length`	MySQL extension
`DATA_FREE`	`Data_free`	MySQL extension
`AUTO_INCREMENT`	`Auto_increment`	MySQL extension
`CREATE_TIME`	`Create_time`	MySQL extension
`UPDATE_TIME`	`Update_time`	MySQL extension
`CHECK_TIME`	`Check_time`	MySQL extension
`TABLE_COLLATION`	`Collation`	MySQL extension
`CHECKSUM`	`Checksum`	MySQL extension
`CREATE_OPTIONS`	`Create_options`	MySQL extension
`TABLE_COMMENT`	`Comment`	MySQL extension

Notes:

TABLE_SCHEMA and TABLE_NAME are a single field in a SHOW display, for example Table_in_db1.
TABLE_TYPE should be BASE TABLE or VIEW. Currently, the TABLES table does not list TEMPORARY tables.
For partitioned tables, beginning with MySQL 5.1.9, the ENGINE column shows the name of the storage engine used by all partitions. (Previously, this column showed PARTITION for such tables.)
The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database.
For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)
Prior to MySQL 5.1.12, MySQL Cluster allocated storage for variable-width columns in 10-page extents of 32 kilobytes each; thus, the DATA_LENGTH for such columns was reported in increments of 320 KB. Beginning with MySQL 5.1.12, the DATA_LENGTH column reflects the true amount of storage for variable-width columns of NDB tables. (Bug #18413)
For NDB tables, DATA_LENGTH includes data stored in main memory only; the MAX_DATA_LENGTH and DATA_FREE columns apply to Disk Data.
Beginning with MySQL Cluster NDB 7.0.22 and MySQL Cluster NDB 7.1.11, for MySQL Cluster Disk Data tables, MAX_DATA_LENGTH shows the space allocated for the disk part of a Disk Data table or fragment. (In-memory data resource usage is reported by the DATA_LENGTH column.)
Beginning with MySQL 5.1.28, the DATA_FREE column shows the free space in bytes for InnoDB tables.
Beginning with MySQL Cluster NDB 7.0.22 and MySQL Cluster NDB 7.1.11, DATA_FREE shows the space allocated on disk for, but not used by, a Disk Data table or fragment on disk. (In-memory data resource usage is reported by the DATA_LENGTH column.)
We have nothing for the table's default character set. TABLE_COLLATION is close, because collation names begin with a character set name.
Beginning with MySQL 5.1.9, the CREATE_OPTIONS column shows partitioned if the table is partitioned.

The following statements 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']

Previous / Next / Up / Table of Contents

User Comments

Posted by Leslie I'Anson on February 11 2009 10:37am

[Delete] [Edit]

My understanding of calculating database size is to add the size of the tables to the size of the indexes; database = table(s) + index(es).

SELECT concat( table_schema, '.', table_name ) table_name,
concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length,
concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size
FROM information_schema.TABLES
ORDER BY data_length DESC;

I've completed some rudimentary tests which seem to confirm this, at least in the case of MySQL databases.

Feedback welcomed!

Posted by Aleksandar Kostadinov on October 12 2009 1:45pm

[Delete] [Edit]

Better would be 'ORDER BY ( data_length + index_length ) DESC' to get proper ordering. As well you can skip the inner round in total_size calculation.
Additionally engine type should be observed. One would care about certain types of tables. For example memory or non-memory. Unfortunately I can't see a consistent way to tell how much real disk space is occupied by a database.

Thanks for the expression though!

Following should show size per database:
SELECT table_schema 'database',
concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size
FROM information_schema.TABLES
WHERE ENGINE=('MyISAM' || 'InnoDB' )
GROUP BY table_schema;

Posted by Kalina Detko on September 2 2010 3:41pm

[Delete] [Edit]

TABLE_ROWS (and perhaps some other columns in INFORMATION_SCHEMA.TABLES) may get out of sync with the current table contents, but you can update it by running ANALYZE.

Posted by Naref Szolik on May 27 2012 1:03pm

[Delete] [Edit]

UPDATE_TIME only stores values for a subset of the available table storage engines.

Posted by Chris Calender on March 7 2013 8:17pm

[Delete] [Edit]

UPDATE_TIME works for MyISAM, not InnoDB.

From my tests, it updated the timestamp on both DML and DDL operations.

Add your own comment.

Top / Previous / Next / Up / Table of Contents

20.20. The INFORMATION_SCHEMA TABLES Table

User Comments

20.20. The `INFORMATION_SCHEMA TABLES` Table