Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 34.7Mb
PDF (A4) - 34.7Mb
PDF (RPM) - 32.6Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.3Mb
HTML Download (RPM) - 7.1Mb
Man Pages (TGZ) - 129.9Kb
Man Pages (Zip) - 185.4Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb


Pre-General Availability Draft: 2018-01-12


The TABLES table provides information about tables in databases.

Columns in TABLES that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry to 0. For more information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.


If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB. For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0.

ENGINEEngineMySQL extension
VERSIONVersionThe .frm file version. With the removal of .frm files in MySQL 8.0, this field now reports a hardcoded value of 10, which is the last .frm file version used in MySQL 5.7.
ROW_FORMATRow_formatMySQL extension
TABLE_ROWSRowsMySQL extension
AVG_ROW_LENGTHAvg_row_lengthMySQL extension
DATA_LENGTHData_lengthMySQL extension
MAX_DATA_LENGTHMax_data_lengthMySQL extension
INDEX_LENGTHIndex_lengthMySQL extension
DATA_FREEData_freeMySQL extension
AUTO_INCREMENTAuto_incrementMySQL extension
CREATE_TIMECreate_timeMySQL extension
UPDATE_TIMEUpdate_timeMySQL extension
CHECK_TIMECheck_timeMySQL extension
TABLE_COLLATIONCollationMySQL extension
CHECKSUMChecksumMySQL extension
CREATE_OPTIONSCreate_optionsMySQL extension
TABLE_COMMENTCommentMySQL extension


  • Refer to SHOW TABLE STATUS for field descriptions.

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

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

  • UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables that are not partitioned. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

  • For partitioned InnoDB tables, the CHECK_TIME column is always NULL.

  • 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:

  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

  FROM db_name
  [LIKE 'wild']

User Comments
  Posted by Leslie I'Anson on February 11, 2009
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
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
GROUP BY table_schema;
  Posted by Kalina Detko on September 2, 2010
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
UPDATE_TIME only stores values for a subset of the available table storage engines.
  Posted by Chris Calender on March 7, 2013
UPDATE_TIME works for MyISAM, not InnoDB.

From my tests, it updated the timestamp on both DML and DDL operations.
Sign Up Login You must be logged in to post a comment.