Using MySQL's INFORMATION_SCHEMA to Find Large Tables
Nov 13, 2018
Every so often it’s a good idea to see where your server’s used hard drive space is going. You can use df to troubleshoot but it’s also nice to have a quick query to see which tables are using the most space. With this information you can determine what to do to fix it (if anything).
Please note: All examples in this article were written using MySQL 5.5 and done in a development environment. Please do not try this in a production environment unless you are sure of the ramifications.
MySQL provides a schema named INFORMATION_SCHEMA that contains a series of tables useful for finding out information about the schema(s) in your database server. Because it’s a schema you can interact with it using SQL commands:
The table we’re interested in for our purposes today is the TABLES table. You can see the results of a select query against it below.
The TABLE_SCHEMA column is the schema that the table exists within and the INFORMATION_SCHEMA.TABLES table contains rows for every schema’s tables so we’re going to want to limit it to just the schema we’re interested in:
Now that we’re looking at just the wordpress tables we need to look at two columns to determine the size of the database the DATA_LENGTH and INDEX_LENGTH. These hold the size of all the data in the table and the size of the indexes in bytes.
Now we’re going to look at adding them together so we can get a better understanding of how much space the table is using and sort the results based on the size so we can quickly see what’s using the most space.
To make is easier to understand we can throw in some division to see the size in megabytes instead of bytes.
Like this post? Don't forget to follow us on Twitter and Facebook for updates.