Tables sorted by size in Megabytes

By Hawkee on Oct 05, 2012

This is a very handy query that sorts your tables by their physical size in megabytes. Just be sure to edit the query to include the database name.

select table_name, engine, table_rows, data_length, round(((data_length+index_length)/1024/1024),0) "MB"  
from information_schema.tables where table_schema = 'MY_DATABASE' order by data_length;

Like Page Follow Author

Comments

Hawkee - Oct 19, 2012

It really depends on what you're doing and how well you know your schema. If you know the schema well and you're just going in to delete some rows or check the processlist the CLI will suffice. A GUI is good for creating tables and learning about the table schema.

Respond

Abcdefmonkey - Oct 19, 2012

Eh, GUI is good. I prefer the GUI, personally. I've used PHPMyAdmin for the longest and I haven't had much trouble. However, using the CLI isn't bad either. It has its advantages over GUI. Although, GUI is best suited for noobies to MySQL or people much like myself. XD Even though I'm not so much a noobie :P

Respond

Hawkee - Oct 10, 2012

@[Plornt] I do like phpMyAdmin when I'm not too familiar with the tables and need to poke around a bit.

Respond

[Plornt] - Oct 10, 2012

Yeah I know what you mean, I meant for more smaller tasks like looking through table data etc. I personally feel its much easier to sift through the data when its laid out in a nicer fasion.

Respond

Hawkee - Oct 09, 2012

The main reason I use the CLI is I'm already there. I do a lot of system administration through the terminal and it's just easier to type "mysql" than to click around my desktop. Plus I don't want the httpd timeout getting in the way of longer queries. I typically run long queries that sometimes take a few minutes to complete. It's better to put those into a "screen" so there's nothing that'll stop them from completing.

Respond

sean - Oct 09, 2012

I'm with @Hawkee as well. CLI does provide far more flexibly and you remove the PHP 'middle-man' with things like large SQL files. I've actually used Navicat for years and it's been great. Not only does it provide minimal client-side GUI but, it allows for CLI access. They've had a lite version out since I can remember but, I'm having an issue finding it at the moment. It's a great tool and I would highly recommend it!

Respond

Hawkee - Oct 07, 2012

@[Plornt] I prefer the flexibility of the command line. I used to use PHPMyAdmin quite a bit, but I've been moving more toward the command line over the years. It's just faster for me.

Respond

[Plornt] - Oct 07, 2012

Ah, this is why I like to use a GUI when going through mysql databases. Its so much easier to be able to use something like PHPMyadmin and selecting the size column - auto sorts it for you. Makes making views etc a lot easier too.

Respond