[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.

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.