Use SQL to show MySQL tables in decreasing size order
Earlier today, I noticed that the robservatory database was massive—over 1GB in size, which it shouldn't be anywhere near (it's usually around 100MB). This is over the database size limit at my host, so I couldn't add any new content (nor could visitors comment, create accounts, etc.)
My host offers phpmyadmin acccess, so I connected to the database to try to figure out what was going on. Using phpmyadmin, you can browse tables, perform SQL commands, and export and import data—it's a must-have tool for managing remote databases.
The first challenge was to figure out which table was causing the problem. To help with that, I wanted to see which WordPress tables were the largest, as that should be a good hint. A web search found lots of possible solutions, but I liked this one the best. Within that thread, I slightly modified one of the queries to do what I wanted:
1 2 3 4 5 6 | SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = 'YOUR_DATABASE_NAME_HERE' ORDER BY (data_length + index_length) DESC; |