← Avi Tzurel

• Archive
• Contact

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

• Tags:
• MySql
• Database