Size of all indexes on a mysql database
indexing, mysql, ssh
Solution
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;
Problem
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.