MySQL is a popular database management system that can be used to store data for many websites. It can be used on both the client and server sides. The client side is the one that you use to access the data while the server side is where MySQL stores the data. There are a lot of tables in MySQL and it can take up a lot of space. To see which tables are taking up the most space, you can use the SHOW TABLES command. This command will show you all of the tables in your database and their sizes. If you want to find out how much space each table takes, you can also use the COUNT(*) command. This command will tell you how many rows are in each table and how big they are.
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.
