I can run this query to get the sizes of all tables in a MySQL database:
show table status from myDatabaseName;
I would like some help in understanding the results. I am looking for tables with the largest sizes.
Which column should I look at?
I can run this query to get the sizes of all tables in a MySQL database:
show table status from myDatabaseName;
I would like some help in understanding the results. I am looking for tables with the largest sizes.
Which column should I look at?
If you have
ssh
access, you might want to simply trydu -hc /var/lib/mysql
(or differentdatadir
, as set in yourmy.cnf
) as well.You can get schema name from "information_schema" -> SCHEMATA table -> "SCHEMA_NAME" column
Additional You can get size of the mysql databases as following.
Result
You can get additional details in here.
This sorts the sizes (DB Size in MB).
Try the following shell command (replace
DB_NAME
with your database name):For Drupal/drush solution, check the following example script which will display the biggest tables in use:
Another way of showing the number of rows and space occupied and ordering by it.
The only string you have to substitute in this query is "yourDatabaseName".
Calculate the total size of the database at the end: