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.
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 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: