How to See Which MySQL Tables Take Up the Most Space
Database backups are one of those things that just keep getting bigger and bigger until they consume all the physical material in the world. Unlike reality TV, we can easily delete things from our databases to make the backups smaller. But what are the things?
MySQL stores meta information about databases and tables in information_schemathe DATABASE table, and specific information about tables in the TABLES table. So all we have to do is run a query to find it.
Hmm. Maybe we need to run a better query.
So data_lengththe column tells us how much data is actually in the table, so maybe if we just include that and sort, we'll get something better... um... now this is in bytes. Okay, enough of the nonsense.
MySQL has two places to store large amounts of data: tables and indexes . We might have a table that stores a million records, but if we also have a bunch of indexes across those columns, we have to duplicate that data and sort it differently and store each piece of data in the index.
So to get the true table size we need to include the index length. While we're at it, it doesn't hurt to convert from bytes to MB . So here's a better query that will give us the top 20 tables across all databases on the server.
select table_schema as db,table_name, round((data_length+index_length) / 1048576,1) as size from information_schema.tables order by data_length+index_length desc limit 20;
This will give you output like this:
If we want to view the table size separately from the index size, we can use a query like this instead:
select table_schema as db,table_name, round((data_length) / 1048576,1) as tablesize,round((index_length) / 1048576,1) as indexsize from information_schema.tables order by data_length desc limit 20;
As you can see, if you are trying to reduce the size of a database on your server, it is worth looking at your indexes, as they can be quite large. However… don’t remove indexes just because they are large – they are very important for speeding up queries, especially against large databases.
And the indexes will not be included in the database backup anyway.
For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.
Related Articles
Two ways to install mysql-5.5.47 on Linux system and manage mysql
Publish Date：2025/04/26 Views：140 Category：MySQL
-
We know that there are generally two ways to install software on the Linux system. One is to use rpm or yum to install, which is convenient and fast; the other is to use the compiled source package. Although this method is more troublesome
Mysql master-slave replication simple configuration
Publish Date：2025/04/26 Views：120 Category：MySQL
-
I'm learning about MySQL master-slave replication recently and want to apply it to a project at work. Since I'm new to it, I don't understand it very well, so I can only share how to make a simple configuration. At the beginning, we configu
Implementation details of MySQL master-slave replication (I) Exploration of the m
Publish Date：2025/04/26 Views：56 Category：MySQL
-
This article mainly discusses the implementation mechanism of master-slave replication, which may not be directly helpful for our actual application, but understanding its principle can achieve twice the result with half the effort for futu
Implementation details of MySQL master-slave replication (II) Exploration from th
Publish Date：2025/04/26 Views：74 Category：MySQL
-
Previously we explored the master server in master-slave replication. Now let's look at how the slave server works in the entire system. In the article Master Server Exploration, we mentioned that three threads are needed in a master-slave
Mysql master-slave replication - what to do if the slave server stops
Publish Date：2025/04/26 Views：97 Category：MySQL
-
You may find this topic a little ridiculous. What can we do if the server stops? Of course, we should restart the service. Restarting the service is no problem. The problem is that a lot of data has been written to the master database durin
MySQL stored procedure details
Publish Date：2025/04/26 Views：163 Category：MySQL
-
A stored procedure can be thought of as encapsulating a SQL statement that we need to process specially into a function. When needed, we only need to call this function to achieve the desired operation. This process can be called a stored p
How many of these MySQL statement tags have you used?
Publish Date：2025/04/26 Views：122 Category：MySQL
-
In the article "A Peek into MySQL Stored Procedure Details" , we briefly introduced the use of stored procedures. The syntax for creating stored procedures includes BEGIN...END. In addition to BEGIN...END, the following statement tags can b
MySQL CURRENT_TIMESTAMP() Function Detailed Introduction (with Examples)
Publish Date：2025/04/26 Views：132 Category：MySQL
-
This article explains how to use MySQL functions with examples CURRENT_TIMESTAMP() . By using it, we can convert or display the current date and time. The output format is "YYYY-MM-DD HH:MM:SS" or "YYYYMMDDHHMMSS", depending on the context
Back up the MySQL database to a file
Publish Date：2025/04/26 Views：166 Category：MySQL
-
Backing up your database is a very important system administration task that should usually cron be run from a job at scheduled intervals. We will use mysqldump the dump utility that comes with mysql to dump the contents of the database to
