7

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.

Kevin
  • 1,685
  • 7
  • 28
  • 55
  • http://stackoverflow.com/questions/781873/how-to-figure-out-size-of-indexes-in-mysql – Greg Mar 19 '13 at 19:18
  • Yeah, I read that question before posting this one. I didn't see how I could see the index size only for the entire db (or by table, but showing all tables)....I'm not looking for table status, but the entire db status. Did I miss it? – Kevin Mar 19 '13 at 19:26

1 Answers1

13

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;
Steve
  • 3,673
  • 1
  • 19
  • 24