[MySQL] Know the size of tables and databases

Knowing precisely the size of a database or the tables in a database can be very interesting for MySQL users. This article presents several SQL queries to be executed, either in an application, on the command line or directly in the interface of PhpMyAdmin to find out the size of databases or tables.
MySQL Logo
Information: connect to MySQL from the command line
To connect to MySQL from the command line under linux it is possible to open a terminal and execute the following command:
$ mysql -u [nom utilisateur] -p
Once this command has been executed, you must fill in the password to connect.
It is then possible to connect to a database using the following query:
$ user [nom base de données];
Database size
The following query is used to list all the databases and calculate the size of each of them in MB.
SELECT table_schema AS NomBaseDeDonnees, ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS BaseDonneesMo FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;
It is also possible to view in detail the size of a specific database. The query below shows the size of a database in MB, KB and also in bytes. You just have to adapt the query by replacing “nom_base_de_donnees” by the database of your choice.
SELECT CONCAT( SUM(ROUND( ( (DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)), 'Mo' ) AS TailleMo, CONCAT( SUM(ROUND( ( (DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 ),2)), 'Ko' ) AS TailleKo, CONCAT( SUM(ROUND( ( (DATA_LENGTH + INDEX_LENGTH - DATA_FREE) ),2)), 'o' ) AS Tailleo FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'nom_base_de_donnees';
Size of tables in a database
To find out the size of each table included in a database, you can run the query below:
SELECT TABLE_NAME, CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024), 2), 'Mo') AS TailleMo FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'nom_base_de_donnees'
To find out only the size of an SQL table, you can run the following query:
SELECT CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024), 2), 'Mo') AS TailleMo FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'nom_base_de_donnees' AND TABLE_NAME = 'nom_table';
Interests of these requests
These SQL queries can be useful to find out if a database or table is getting too large. For example, it might be possible to create a web interface that displays the weight of your tables and that could even send an alert as soon as a table becomes too big. This allows you to better understand the space occupied by the data and thus delete data if a database becomes too large.