
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How to get the size of the tables of a MySQL database?
To get the size of the tables of a MySQL database, you can use the “information_schema.tables”.
Here is the syntax to know the size of all tables.
SELECT TABLE_NAME AS `ALLTABLESNAME`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `TABLESIZEIN(MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "yourDatabaseName" ORDER BY (DATA_LENGTH + INDEX_LENGTH) ASC;
Let us apply the above syntax to get the size of the tables.
mysql> SELECT TABLE_NAME AS `ALLTABLESNAME`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `TABLESIZEIN(MB)` -> FROM information_schema.TABLES WHERE TABLE_SCHEMA = "business" -> ORDER BY (DATA_LENGTH + INDEX_LENGTH) ASC;
Sample Output.
+------------------------------------------------------------------+-----------------+ | ALLTABLESNAME | TABLESIZEIN(MB) | +------------------------------------------------------------------+-----------------+ | studentrecordwithmyisam | 0 | | querydatedemo | 1 | | rownumberdemo | 0 | | secondtable | 0 | | spacecolumn | 2 | | table1 | 0 | | tbldemotrail | 2 | | tblstudent | 0 | | timestamptodatedemo | 1 | | uniqueconstraintdemo | 0 | | usernameandpassworddemo | 2 | | addingunique | 5 | | bookindexes | 0 | | tblf | 0 | | uniquedemo | 2 | | multipleindexdemo | 0 | | uniquedemo1 | 0 | | foreigntable | 5 | | tabledemo2 | 0 | | foreigntabledemo | 2 | | studentenrollment | 0 | | tabledemo3 | 0 | | duplicatebookindexes | 0 | | clonestudent | 2 | | student | 0 | +------------------------------------------------------------------+-----------------+ 26 rows in set (10.29 sec)
- Related Articles
- How to get the list of tables in default MySQL database?
- How Can I check the size of the tables in a particular MySQL database?
- How to count the number of tables in a MySQL database?
- Get a list of non-empty tables in a particular MySQL database?
- How to check table status of the tables in a particular MySQL database?
- While connecting to one MySQL database, how can I see the list of tables of other MySQL database?
- How can we get the list of tables in a particular database from MySQL Server command line?
- Get the storage size of a database in MongoDB?
- What is the MySQL query to display the number of tables in a database?
- Get record count for all tables in MySQL database?
- How do I select four random tables from a MySQL database having thousands of tables?
- Which tables are used to control the privileges of MySQL database server?
- Get all the tables from a MySQL database having a specific column, let’s say xyz?
- How can we check the character set of all the tables in a particular MySQL database?
- List down all the Tables in a MySQL Database

Advertisements