MySQL / MariaDB: Show disk usage of tables and columns

You can find out about disk space usage of all tables within your database by running this:

SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "$your_database";

Replace $your_database here.

To find out the disk usage of a single column:

SELECT sum(char_length($your_column))/1024/1024 FROM $your_table

Result is in megabytes again.

Thomas Eisenbarth

makandra.de

Say thanks23

Last edit

2016-04-06

Thomas Eisenbarth

License

Source code in this card is licensed under the MIT License.

Show the character set and the collation of your MySQL tables

To show the collation of your tables you have to login to the MySQL console and execute SHOW TABLE STATUS FROM database;

mysql> SHOW TABLE STATUS FROM test;
+-------------+--------+---------+------------+------+----------------+--------...

Show and change MySQL default character set

To show the MySQL default character set you have to login to the MySQL console and execute SHOW VARIABLES LIKE 'char%';

mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------+
| Variable_name  ...

SQL: Find out number of rows of all tables within a MySQL database

Here you are:

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database' order by table_rows;

MySQL: How to dump single tables instead of a complete database

Sometimes you want to test migrations with production or staging data. Dumping single tables makes sense if a complete dump would be to big.

mysqldump -u deploy_user -p application_production table1 table2 table2 > table1_table2_table2.sql.du...

Show a MySQL table's charset, collation and engine

Use this MySQL command to show further info about a table:

SHOW CREATE TABLE tags;

This will output a table schema like this:

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unico...

Posted by Thomas Eisenbarth to makandra dev (2016-04-06 08:36)

MySQL / MariaDB: Show disk usage of tables and columns

Related cards: