How to get table size in mysql


You can run this command on your MySQL database to check table size. Please follow all the steps.

Step 1:Check your  MySQL server status

# lsof -i :mysql
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 24709 mysql 19u IPv6 398627 0t0 TCP *:mysql (LISTEN)

Step 2:Connect MySQL database

-At my test server, I used xamp server MySQL.So MySQL command located under lampp directory. So if you installed Linux  MySQL your command probably located under  /bin or  /usr/bin.

root@redhattemplate ~]# /opt/lampp/bin/mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2014
Server version: 10.1.8-MariaDB Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
|  information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]>

Step 3:Select databases

MariaDB [(none)]> use phpmyadmin;
Database changed
MariaDB [phpmyadmin]> 

Step 4:Check all tables

MariaDB [phpmyadmin]> show tables;
+------------------------+
| Tables_in_phpmyadmin |
+------------------------+
| pma__bookmark |
| pma__central_columns |
| pma__column_info |
| pma__designer_settings |
| pma__export_templates |
| pma__favorite |
| pma__history |
| pma__navigationhiding |
| pma__pdf_pages |
| pma__recent |
| pma__relation |
| pma__savedsearches |
| pma__table_coords |
| pma__table_info |
| pma__table_uiprefs |
| pma__tracking |
| pma__userconfig |
| pma__usergroups |
| pma__users |
+------------------------+
19 rows in set (0.00 sec)

MariaDB [phpmyadmin]>

Step 5:Check size  of tables

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

TEST Command:

MariaDB [phpmyadmin]> SELECT table_schema as `Database`, table_name AS `Table`,
-> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
-> FROM information_schema.TABLES
-> ORDER BY (data_length + index_length) DESC;
+--------------------+----------------------------------------------------+------------+
| Database | Table | Size in MB |
+--------------------+----------------------------------------------------+------------+
| mysql | time_zone_transition | 4.01 |
| mysql | time_zone_transition_type | 0.39 |
+--------------------+----------------------------------------------------+------------+
198 rows in set, 19 warnings (0.02 sec)

MariaDB [phpmyadmin]>
Tagged In:

I'm a IT Infrastructure and Operations Architect with extensive experience and administration skills and works for Interbank Card Center Of Turkey(BKM). I provide hardware and software support for the IT Infrastructure and Operations tasks.

195 Total Posts
Follow Me

Related Post

0 Comments

Leave a Reply