«
»

Get MySQL Table Size

MySQL table size

MySQL Table Size

What to do if you need to know some MySQL database table real size? Thanks to MySQL, it is not very complex task. There is no need to know the table row length and multiply it on quant of rows in that table. Just remember about MySQL system database called information_schema. It has right information for those who want to get DB table real size. Let’s look into the information_schema.tables table. We will use next fields from it:
- TABLE_SCHEME (database name),
- TABLE_NAME (it is self-explaining),
- TABLE_ROWS (quant of rows in the table),
- DATA_LENGTH (table data length in bytes),
- INDEX_LENGTH (table indexes length in bytes)
Let’s suppose that our database is named as wpmu, take your favorite MySQL client tool (I use SQLyog Community Edition – MySQL GUI product) and try this query:

SELECT TABLE_NAME AS "Table Name", 
      table_rows AS "Quant of Rows", 
      ROUND((data_length + index_length)/1024,2) AS "Total Size Kb"
  FROM information_schema.TABLES
  WHERE information_schema.TABLES.table_schema='wpmu'

The result can be looked as:

MySQL tables size query result

MySQL tables size query result

Tags: MySQL, sql

This entry was posted on Tuesday, March 30th, 2010 at 11:24 and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

  • http://www.happycampingblog.com/ Kelle Woehr

    good!it’s very useful!thx!