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