Getting Table Sizes in MySQL

Sometimes, like when evaluating an open source project, you might want to understand how the database entries grow based on your typical usage. This will allow you to find the hot tables i.e. the ones that grow the fastest and will help you plan ahead to make sure it scales. As this is something that would usually take place in a development environment or on a local machine you should have the root access to the database. The examples bellow assume your password is mypwd and schema name is schema1.

The simplest way to achieve this by querying the INFORMATION_SCHEMA.TABLES table.

INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'schema1' ORDER BY TABLE_ROWS
desc;"

This will work if you’re trying to get a general idea of the table sizes. But keep in mind that this info is not updated with each row that is inserted or deleted so the information might be stale.

To get a more accurate representation of the table sizes we could get the table names from INFOMRATION_SCHEMA and use a SELECT COUNT(*) query for each table. But I’m not even going to provide this example as it would mean we’re doing N+1 queries where N is the number of tables in the schema which is very inneficient. Instead we’re going to leverage the ability to create a MySQL procedure with many internal queries which will appear as a single query to the caller. To gather the table sizes we’re going to use the following procedure:

USE schema1;

DROP PROCEDURE IF EXISTS GetTableCounts;

DELIMITER //

CREATE PROCEDURE GetTableCounts()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tbl_name VARCHAR(255);
DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'schema1';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
  FETCH cur INTO tbl_name;
  IF done THEN
    LEAVE read_loop;
  END IF;

  SET @sql = CONCAT('SELECT COUNT(*) AS Row_Count, \'', tbl_name, '\' AS Table_Name FROM ', tbl_name);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END LOOP;

CLOSE cur;
END //

DELIMITER ;

Execute this in your MySQL client by pasting or you can save this to a file and do mysql -uroot -pmypwd -h 127.0.0.1 < create_proc.sql to run it.

Now we’re set up to run the procedure as often as we want and it’s much faster than individual queries. Enjoy it with mysql -uroot -pmypwd -h 127.0.0.1 -D schema1 -s -N -e "CALL GetTableCounts();" the additional params remove some formatting that mysql client usually adds, otherwise it would look like multiple outputs instead of one. To finalize it you can add some formatting and sorting in the command line.

mysql -uroot -pmypwd -h 127.0.0.1 -D schema1 -s -N -e "CALL GetTableCounts();" | column -t | sort -n