Skip navigation links
MySQL 5.5 Reference Manual :: 19 INFORMATION_SCHEMA Tables :: 19.22 The INFORMATION_SCHEMA TABLES Table
19.23 The INFORMATION_SCHEMA TABLESPACES Table »
Section Navigation      [Toggle]

19.22. The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

INFORMATION_SCHEMA Name SHOW Name Remarks
TABLE_CATALOG   def
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, 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 NDB tables, DATA_LENGTH includes data stored in main memory only; the MAX_DATA_LENGTH and DATA_FREE columns apply to Disk Data.

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

  • The DATA_FREE column shows the free space in bytes for InnoDB tables.

    For MySQL Cluster, 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.

  • 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']
Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices

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.

Add your own comment.