Optimize in MySQL

[ollmorris]

Can anyone tell me how to work out the size (in KB or similar) of a database. In one of my scripts I use the Optimize command in MySQL as there is a lot of data added and removed from the database. A quick trawl through Google and MySQL.com as well as browsing through PHPMyAdmin code has left me without a clue.

Any help would be greatfully appreciated.

[inerte]

SHOW TABLE STATUS LIKE 'tablename'

http://dev.mysql.com/doc/mysql/en/show-table-status.html

[LordShryku]

The way phpAdmin does it, if I remember right, is to use a SHOW TABLE STATUS [database name] query. This will bring back a row for each table in the database. From there, you loop through each table and the (Data Length + Index Length) = table size in bytes. Loop through each table adding those to the mix to get the total for the database. Then divide by 1024 to get kilobytes, divide again for megs, so on and so forth.