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"/>
Thank you for your interest!

We will contact you as soon as possible.

Send us a message

Oops, something went wrong
Please try again or contact us by email at info@tikalk.com