MySQL stores meta information about databases and tables in the information_schema database, and information specifically about tables is stored in the TABLES table. So all we have to do is run a query to look it up.
Hmm. Maybe we’ll need to run a better query.
So the data_length column gives us how much data is actually in the tables, so maybe if we just include that and sort, we’ll get something better… hmm… now this is in bytes. Alright, enough messing around.
MySQL has two places where huge sets of data are stored: tables, and indexes. You might have a table that stores a million records, but if you also have a bunch of indexes across many of those columns, that data has to be duplicated and sorted a different way and stored in the index for each one.
So to get a true table size, we’re going to need to include the index length. And it wouldn’t hurt to convert from bytes to MB instead, while we’re at it. So here’s a better query that’ll give you the top 20 tables for all databases on your server.
Which will give you output something like this:
if you want to see the table size separately from the index size, you can use a query like this instead:
As you can see, it’s worth looking at your indexes if you’re trying to reduce the size of your database on the server, as they can be enormous. But… do not delete indexes just because they are big — they are incredibly important for speeding up queries, especially against large databases.
And indexes don’t get included in the database backups anyway.
