MySQL databases are generally pretty good about dealing with large size tables with indexes, however sometimes it’s good to know just how large a DB is so you can optimize it if necessary. In a lot of cases it’s not very easy to just look up the size of the table, and as such, the ability to run a SQL query to return that info is helpful. Here is the query that can help:
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "YOUR_TABLE_NAME" ORDER BY (data_length + index_length) DESC;
Be sure to update the YOUR_TABLE_NAME to the name of your DB table.
To get DB size on all DB’s in an instance:
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema
This will return the full DB size for all tables on a MySQL/Maria instance.
