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.
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:
| DB_NAME | TABLE_NAME | TABLE_SIZE_in_GB |
|-----------|------------|-------------------|
| your_db1 | table1 | 10.23 |
| your_db2 | table2 | 8.76 |
| ... | ... | ... |