How Do I Check MySQL Database Usage?

Has your MySQL database exceeded the size limit? This MySQL Select statement will help you review which tables are using the most space. The Select statement below return a list of all of the tables in the database, how many rows in the table, and the total disk space size each table is using. 

SELECT
  	CONCAT( 'TOTALS (', COUNT(TABLE_NAME),')' ) AS `Tables`,
	SUM(TABLE_ROWS) AS `Rows`,
	ROUND( (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) / 1024 / 1024) AS `Total-Size (MB)`,
	ROUND( SUM(DATA_LENGTH) / 1024 / 1024) AS `Data-Size (MB)`,
	ROUND( SUM(INDEX_LENGTH) / 1024 / 1024) AS `Index-Size (MB)`
FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_ROWS IS NOT NULL 
UNION
SELECT
  	TABLE_NAME AS `Table`,
	SUM(TABLE_ROWS) AS `Rows`,
  	ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Total-Size (MB)`,
	ROUND(DATA_LENGTH / 1024 / 1024) AS `Data-Size (MB)`,
	ROUND(INDEX_LENGTH / 1024 / 1024) AS `Index-Size (MB)`
FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_ROWS IS NOT NULL 
GROUP BY TABLE_NAME
ORDER BY `Rows` DESC;

The statement results will looks something like this:

(note: I removed my table names for security reasons. It’s better not to give hackers clues about the website)

Database Usage Query Results

Here are the details on whats included in the results table when checking your Database Usage:

• TOTALS (n) row: the first row shows the totals for all tables in the database. 
• Table column: shows the name of the table 
• Rows column: shows the total number of rows for this table 
• Total-Size column: shows the total disk space (data and index) used by this table, in megabytes  
• Data-Size column: shows the disk space used by all fields in this table, in megabytes  
• Index-Size column: shows the disk space used by all indexes for this table, in megabytes

Query Sort Options

The query above is sorting by row count in descending order ( ORDER BY `Rows` DESC ) but you can change that to sort by disk space like this ( ORDER BY `Total-Size (MB)` DESC )

How To Run A MySQL Query

To run a MySQL query, go into your web hosting account and start the phpMyAdmin app. phpMyAdmin is a web based client tool that connects with a MySQL Database Server. In phpMyAdmin select your database by name, go to the SQL tab and copy and paste in the query, then press the “Go” button. Your results will be displayed below the query.

If you find yourself working in the database often a MySQL client that runs on your desktop will be easy and quicker. Take a look at Navicat for MySQL, or MySQL Workbench. I like Navicat.

INFORMATION_SCHEMA

The database size information for this query comes from the MySQL “information_schema” database. The SQL View called “TABLES” is generated and maintained by MySQL database server. Filter out empty TABLE_ROWS field for an easy way to review your tables:
WHERE information_schema.TABLES.TABLE_ROWS IS NOT NULL

DATA_LENGTH and INDEX_LENGTH fields are the length of the data file and index file, in bytes. Add DATA_LENGTH and INDEX_LENGTH together to get the total disk space each table is using up. I found it easiest to review database usage megabytes in by converted bytes to megabytes. That’s simply (bytes / 1024 / 1024) = megabytes.

Three Part Naming

A MySQL query assumes you are looking at the current database. With Three Part Naming you can query another database. Looking over the fence so to speak. I’m using Three Part Naming here so this query can be executed in your database and get the data from the information_schema database. The three parts of Three Part Naming are: {database}.{table|view}.{field}

The Three Part Naming used above is: information_schema.TABLES.TABLE_ROWS