MySQL Preacher

During the course of my daily work I occasionally search for mysql queries which are cool and helpful. I once found the following query on http://forge.mysql.com/:

SELECT table_name article_attachment,
engine,
ROUND(data_length/1024/1024,2) total_size_mb,
ROUND(index_length/1024/1024,2) total_index_size_mb,
table_rows
FROM information_schema.tables
WHERE table_schema = ‘dbname’
ORDER BY 3 desc;

A generally  lite version is:

select table_schema, table_name, (data_length)/pow(1024,2) AS ‘Data Size in Meg’, (index_length)/pow(1024,2) AS ‘Index Size in Meg’  from tables order by 3 desc;

You can add or remove columns etc and but this query shows the table size (data wise) index size, approx number of rows, size in MB etc. If you would like to know what else is available to add to this query, just do a “desc tables” while using the informations chema (database).

Beginner general information, information_schema, table size