Size of all indexes on a mysql database

Question

There are 6 databases on my server.

In phpmyadmin, I can view the list of tables that comprise a database and see things like # of records, table name, table type, etc. It also shows the size and a total size. The total size of one database is 5 gigs and 80 different tables.

I want to know how much of this 5 gigs is related to my indexes. I can click on a table and view the details there. Theoretically, if I did this 80 times, once for each table, then added it up, I would have my answer.

Is there a way (in phpmyadmin or SSH) to get the answer of how much space the combined indexes of a single mysql table? It would be even better if I could see a list by table and also a sum up.

Thanks in advance.

Answer 1

That's a great question, and yes its possible to view on a database level.

The information is held within the INFORMATION_SCHEMA table

SELECT table_name,
concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'Mb' ) AS 'data_length_mb',
concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'Mb' ) AS 'index_length_mb',
concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'Mb' )  AS 'total_size_mb'
FROM information_schema.tables
WHERE table_schema ='your_db_name'
ORDER BY data_length desc;