Menu
I am not entirely sure what information_schema.TABLES.DATA_FREE means in MySQL.
Can someone please help me understand it?
Thank you.
523
INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.
information_schema is the database where the information about all the other databases is kept, for example, names of a database or a table, the data type of columns, access privileges, etc. It is a built-in virtual database with the sole purpose of providing information about the database system itself.
The syntax to get all table names with the help of SELECT statement. mysql> use test; Database changed mysql> SELECT Table_name as TablesName from information_schema.
The information_schema database (often called I_S for brevity) is a virtual database that contains informative tables. These tables can be divided into several groups: Metadata tables: Tables such as SCHEMATA , TABLES , and COLUMNS contain information about the structure of databases, tables, columns, and so on.
DATA_FREE- The number of allocated but unused bytes. It is the size of the database files compared to the data stored in the database files. PHPMyAdmin shows this information as 'Overhead in tables'.
However for InnoDB this is important- "InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace.". So with a typical InnoDB setup ('innondb_file_per_table' is not set) you will get the free space for all tables and not for a single table.
Refer: http://dev.mysql.com/doc/refman/5.5/en/tables-table.html and http://dev.mysql.com/doc/refman/5.5/en/show-table-status.html
137
According to MySQL's information on MySQL 5.6:
The DATA_FREE column shows the free space in bytes for InnoDB tables
For earlier versions like 5.5:
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.)
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
