Search the MySQL manual:

4.5.7.2 SHOW TABLE STATUS

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

SHOW TABLE STATUS (new in Version 3.23) works likes SHOW STATUS, but provides a lot of information about each table. You can also get this list using the mysqlshow --status db_name command. The following columns are returned:

Column Meaning
Name Name of the table.
Type Type of table. See section 7 MySQL Table Types.
Row_format The row storage format (Fixed, Dynamic, or Compressed).
Rows Number of rows.
Avg_row_length Average row length.
Data_length Length of the datafile.
Max_data_length Max length of the datafile. For fixed row formats, this is the max number of rows in the table. For dynamic row formats, this is the total number of data bytes that can be stored in the table, given the data pointer size used.
Index_length Length of the index file.
Data_free Number of allocated but not used bytes.
Auto_increment Next autoincrement value.
Create_time When the table was created.
Update_time When the datafile was last updated.
Check_time When the table was last checked.
Create_options Extra options used with CREATE TABLE.
Comment The comment used when creating the table (or some information why MySQL couldn't access the table information).

InnoDB tables will report the free space in the tablespace in the table comment.

User Comments

Posted by [name withheld] on Sunday March 9 2003, @7:51pm[Delete] [Edit]

If my disk space is not enough,I want to free the data_free space,How can I do?

Posted by [name withheld] on Sunday April 27 2003, @11:40pm[Delete] [Edit]

Maybe you can define the size of innodb tablespace in .my.cnf while starting mysql for the first time.

Posted by Thomas Healy on Tuesday May 27 2003, @6:08am[Delete] [Edit]

What does a max_data_length of "null" mean when you do a show table? Do MY SQL tables auto grow or do you need to keep allocating additional space?

Posted by VIGNALS Fabrice on Friday June 6 2003, @10:54pm[Delete] [Edit]

@ Thomas :
NULL = unlimited;
In some case ( max configs for row, col, memory, disk space, buffer ... ), 0 or Null in MySQL config mean no limit, or max limit ( so the real limit is the software, or bad way, the hardware limit ).
Unlimited setting is not recommanded, on httpd server that u cant control in real time.
Use unlimited, when you can restart, stop, mysql or the server ( i use that setting only on local server )

Add your own comment.