Minimising MYSQL disk usage

Database optimisation is hard. Whether it’s tweaking a query or digging into my.cnf to change some of the global MYSQL parameters, you can actually make matters worse without intending to. But one thing that is simple, easy and predictable is making the database smaller.

Knowing which tables are taking up loads of space can be done crudely just by looking at each table to find those with lots of rows, but a when you’ve got a behemoth like Magento, or even a WordPress install with plugin powered table bloat, it’s slow and boring looking at each table. A query is much easier…

SET @database_name = "whatever_your_database_is_called";
SELECT TABLE_NAME AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) AS size
FROM information_schema.TABLES
WHERE table_schema = @database_name
ORDER BY size DESC;

Then it’s just a matter of running some table maintenance to shrink things a bit…