In database management, understanding the size of your tables is crucial for optimizing performance and ensuring efficient resource utilization. If you’re a technical enthusiast or a seasoned database administrator working with MariaDB, this blog post is tailored just for you. Today, we’ll unravel the mystery behind MariaDB database table sizes using a powerful SQL query.

1
2
3
4
5
6
7
8

SELECT 
  table_schema AS DB_NAME, 
  TABLE_NAME, 
  (DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 AS TABLE_SIZE_in_MB
FROM 
  information_schema.TABLES
ORDER BY 
  TABLE_SIZE_in_MB DESC

Exploring MariaDB Table Sizes

MariaDB stands out as a robust and reliable option. To gain insights into your database’s table sizes, we’re going to wield the mighty SQL language. Our tool of choice is a concise yet potent query that taps into the depths of MariaDB’s information schema.

How this works

Let’s break down this SQL incantation to understand its potency:

  • SELECT: Initiates the query to retrieve specific columns from the specified tables.

  • table_schema AS DB_NAME: Fetches the database name from the information schema and aliases it as DB_NAME for clarity.

  • TABLE_NAME: Retrieves the name of each table in the database.

  • (DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 AS TABLE_SIZE_in_GB: Calculates the total size of the table by adding the data length and index length, then converts the result from bytes to gigabytes.

  • FROM information_schema.TABLES: Specifies the source of information, which is the TABLES table within the information schema.

  • ORDER BY TABLE_SIZE_in_GB DESC: Sorts the results in descending order based on the table size, providing a clear view of the largest tables first.

The Output

The output of our SQL wizardry retains its elegance in the Markdown realm, ensuring seamless integration with Hugo. Here’s a snippet of what you can expect:

1
2
3
4
5

| DB_NAME   | TABLE_NAME | TABLE_SIZE_in_GB |
|-----------|------------|-------------------|
| your_db1  | table1     | 10.23             |
| your_db2  | table2     | 8.76              |
| ...       | ...        | ...               |