Get information about all your mysql database tables with a single query
I am working on tweaking our database these days, doing tons of work on making the queries faster, making sure the users are not being delayed by infrastructure stuff.
One piece of info I wanted was to get a quick birds eye view of all the tables I have in the database, how many rows, what is the avg row length (for index purpose), size in MB and the size of the index in MB.
All you need to do is exeucute this query (set the DB name to your database name and you are done).
set @database='db_name'; select table_name, engine, row_format, table_rows, avg_row_length, (data_length+index_length)/1024/1024 as total_size, (index_length)/1024/1024 as index_size from information_schema.tables where table_schema=@database order by 6 desc;
As always, I would love your feedback
<img src="http://feeds.feedburner.com/~r/KensoDev-en/~4/lOOZrNdA7mw" height="1" width="1"/>