The Ultimate Guide to High Availability with MariaDB

Information Schema TABLES Table

You are viewing an old version of this article. View the current version here.

Contents

  1. Examples
    1. View Tables in Order of Size
  2. See Also

The Information Schema table shows information about the various non-TEMPORARY tables (except tabes from Information Schema database) and views on the server.

It contains the following columns:

ColumnDescription
TABLE_CATALOGAlways def.
TABLE_SCHEMADatabase name.
TABLE_NAMETable name.
TABLE_TYPEOne of BASE TABLE for a regular table, VIEW for a view, SYSTEM VIEW for Information Schema tables or SYSTEM VERSIONED for system-versioned tables.
ENGINEStorage Engine.
VERSIONVersion number from the table's .frm file
ROW_FORMATRow format (see InnoDB, Aria and MyISAM row formats).
TABLE_ROWSNumber of rows in the table. Some engines, such as XtraDB and InnoDB may store an estimate.
AVG_ROW_LENGTHAverage row length in the table.
DATA_LENGTHFor InnoDB/XtraDB, the index size, in pages, multiplied by the page size. For Aria and MyISAM, length of the data file, in bytes. For MEMORY, the approximate allocated memory.
MAX_DATA_LENGTHMaximum length of the data file, ie the total number of bytes that could be stored in the table. Not used in XtraDB and InnoDB.
INDEX_LENGTHLength of the index file.
DATA_FREEBytes allocated but unused. For InnoDB tables in a shared tablespace, the free space of the shared tablespace with small safety margin. An estimate in the case of partitioned tables - see the PARTITIONS table.
AUTO_INCREMENTNext AUTO_INCREMENT value.
CREATE_TIMETime the table was created.
UPDATE_TIMETime the table was last updated. On Windows, the timestamp is not updated on update, so MyISAM values will be inaccurate. In InnoDB, if shared tablespaces are used, will be NULL, while buffering can also delay the update, so the value will differ from the actual time of the last UPDATE, INSERT or DELETE.
CHECK_TIMETime the table was last checked. Not kept by all storage engines, in which case will be NULL.
TABLE_COLLATIONCharacter set and collation.
CHECKSUMLive checksum value, if any.
CREATE_OPTIONSExtra CREATE TABLE options.
TABLE_COMMENTTable comment provided when MariaDB created the table.
MAX_INDEX_LENGTHMaximum index length (supported by MyISAM and Aria tables). Added in MariaDB 10.3.5.
TEMPORARYPlaceholder to signal that a table is a temporary table. Currently always "N", except "Y" for generated information_schema tables and NULL for views. Added in MariaDB 10.3.5.

Although the table is standard in the Information Schema, all but TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE and VERSION are MySQL and MariaDB extensions.

SHOW TABLES lists all tables in a database.

Examples

MariaDB [test]> select * from information_schema.tables where table_schema='test'\G
*************************** 1. row ***************************
   TABLE_CATALOG: def
    TABLE_SCHEMA: test
      TABLE_NAME: xx5
      TABLE_TYPE: BASE TABLE
          ENGINE: InnoDB
         VERSION: 10
      ROW_FORMAT: Dynamic
      TABLE_ROWS: 0
  AVG_ROW_LENGTH: 0
     DATA_LENGTH: 16384
 MAX_DATA_LENGTH: 0
    INDEX_LENGTH: 0
       DATA_FREE: 0
  AUTO_INCREMENT: NULL
     CREATE_TIME: 2020-11-18 15:57:10
     UPDATE_TIME: NULL
      CHECK_TIME: NULL
 TABLE_COLLATION: latin1_swedish_ci
        CHECKSUM: NULL
  CREATE_OPTIONS: 
   TABLE_COMMENT: 
MAX_INDEX_LENGTH: 0
       TEMPORARY: N
*************************** 2. row ***************************
   TABLE_CATALOG: def
    TABLE_SCHEMA: test
      TABLE_NAME: xx4
      TABLE_TYPE: BASE TABLE
          ENGINE: MyISAM
         VERSION: 10
      ROW_FORMAT: Fixed
      TABLE_ROWS: 0
  AVG_ROW_LENGTH: 0
     DATA_LENGTH: 0
 MAX_DATA_LENGTH: 1970324836974591
    INDEX_LENGTH: 1024
       DATA_FREE: 0
  AUTO_INCREMENT: NULL
     CREATE_TIME: 2020-11-18 15:56:57
     UPDATE_TIME: 2020-11-18 15:56:57
      CHECK_TIME: NULL
 TABLE_COLLATION: latin1_swedish_ci
        CHECKSUM: NULL
  CREATE_OPTIONS: 
   TABLE_COMMENT: 
MAX_INDEX_LENGTH: 17179868160
       TEMPORARY: N
2 rows in set (0.001 sec)
...

View Tables in Order of Size

Returns a list of all tables in the database, ordered by size:

SELECT table_schema as `DB`, table_name AS `Table`, 
  ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` 
  FROM information_schema.TABLES 
  ORDER BY (data_length + index_length) DESC;

+--------------------+---------------------------------------+-----------+
| DB                 | Table                                 | Size (MB) |
+--------------------+---------------------------------------+-----------+
| wordpress          | wp_simple_history_contexts            |      7.05 |
| wordpress          | wp_posts                              |      6.59 |
| wordpress          | wp_simple_history                     |      3.05 |
| wordpress          | wp_comments                           |      2.73 |
| wordpress          | wp_commentmeta                        |      2.47 |
| wordpress          | wp_simple_login_log                   |      2.03 |
...

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.