Getting a quick summary on your MySQL Database

Summary of yer tables

Sometimes, you just want to see what’s going on from a high level. “HOW CAN I GET A SUMMARY OF MY TABLES?!” you may have asked yourself…

To do this, you’ll need to do a simple query into the TABLES table in the information_schema database. Like this:

mysql> use information_schema;
Database changed

mysql> 

It’s a habit for me, but I always like to describe the table so I know what I’m looking at:

mysql> desc TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

mysql> 

Now, we can see what we’re looking for. Here’s what I generally like to do:

mysql> select TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT from TABLES where TABLE_SCHEMA='werd';
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
| TABLE_NAME            | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH  | INDEX_LENGTH | AUTO_INCREMENT |
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
| wp_commentmeta        |          0 |              0 |           0 |  281474976710655 |         4096 |              1 |
| wp_comments           |          1 |            228 |         228 |  281474976710655 |         7168 |              2 |
| wp_hook_list          |          0 |              0 |       16384 |                0 |            0 |           NULL |
| wp_links              |          0 |              0 |           0 |  281474976710655 |         1024 |              1 |
| wp_options            |        154 |           2548 |      430244 |  281474976710655 |        13312 |            835 |
| wp_postmeta           |          9 |             44 |         404 |  281474976710655 |        10240 |             20 |
| wp_posts              |          8 |            298 |        2388 |  281474976710655 |        11264 |             17 |
| wp_term_relationships |          3 |             21 |          63 | 5910974510923775 |         3072 |           NULL |
| wp_term_taxonomy      |          1 |             40 |          40 |  281474976710655 |         4096 |              2 |
| wp_terms              |          1 |             40 |          40 |  281474976710655 |        11264 |              2 |
| wp_usermeta           |         19 |             61 |        1160 |  281474976710655 |        10240 |             20 |
| wp_users              |          1 |             92 |          92 |  281474976710655 |         4096 |              2 |
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
12 rows in set (0.00 sec)

mysql> 

Yeah, even my schema names are trendy and clever.</sarcasm>

Anyways, now you can sort and/or select however you want! Grok yer data, amigo. =]

I realize my code blocks are not blocking well enough. Will fix.