Skip navigation links
MySQL 5.1 with Maria Reference Manual :: 19 INFORMATION_SCHEMA Tables :: 19.2 The INFORMATION_SCHEMA TABLES Table
19.3 The INFORMATION_SCHEMA COLUMNS Table »
Section Navigation      [Toggle]

19.2. 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.)

  • For tables using the NDBCLUSTER storage engine, beginning with MySQL 5.1.12, the DATA_LENGTH column reflects the true amount of storage for variable-width columns. (See Bug#18413.)

    Note

    Because MySQL Cluster allocates storage for variable-width columns in 10-page extents of 32 kilobytes each, space usage for such columns is reported in increments of 320 KB.

  • Beginning with MySQL 5.1.28, the DATA_FREE column shows the free space in bytes for InnoDB tables.

  • 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']

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;

Add your own comment.