Database Statistics

Ever wonder how much space your database is utilizing? Usually something we don’t give much thought to, however if you are looking into migrating hosting then it is something you may need. I tend to work on a lot of WordPress Sites that utilize MySQL databases and the below 2 queries I have found to be very useful.

Database Size

Adding the data_length and index_length of a table gives you its size. By summing those values for every table we can get the overall size of the Database.

Size in GB

SELECT sum(round(((data_length + index_length) / 1024 / 1024 / 1024), 3)) as "Size in GB" 
FROM information_schema.TABLES 
WHERE table_schema = ""

Size in MB

SELECT sum(round(((data_length + index_length) / 1024 / 1024), 2)) as "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = ""

Table Row Size

Another variable that can impact your decision is your tables row count. To get an exact number you’d have to do a count(*) from the table but this can be a little slow on performance. Another option is to query table_rows property.

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = ""
ORDER BY table_rows desc;

Hope you find these queries helpful.

// Analytics // SQL //

Comments & Questions

Add Your Comment