Quick Tip: Get the Size of a MySQL Database

[ad_1]

TL;DR: In this quick tip, I provide a simple query for determine the size of a database (that includes all tables). This is useful when determine what the largest tables are, especially if there are custom tables in your WordPress installation, and how large they actually are.

Get the Size of a MySQL Database

To determine the size of a MySQL database, you can run the following query (I’ll explain the details of it after the code):

SELECT
	table_schema "Database Name",
	ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM
	information_schema.tables
GROUP BY
	table_schema;

This will generate two columns:

  1. The name of the database,
  2. The size of the database in megabytes.

That that this uses the tables property in the information_schema database to help generate the size of each database. Functions that are helpful to understand here are:

  1. round, will take the values placed in the function and round them to two decimal places (by default; there are other arguments to be passed).
  2. sum, as if it wasn’t evident, adds the total of the values placed in the function.
  3. Dividing the results by 1024 twice is what will yield megabytes (as opposed to bytes or kilobytes).

And note the other values such as data_length and index_length come from information already stored in the database.

Additional Tools

For reference, I recommend the following tools:

[ad_2]

Source link