MySQL Query for Table Record Count (All Tables)

  • 7020618
  • 15-Nov-2013
  • 27-Oct-2017

Environment

MySQL 5.x+
Retain 2.x upgrade to 3.x

Situation

I would like to upgrade from Retain 2.x to 3.x and I am trying to estimate how long the database migration part of the upgrade will take.  Or, I would like to upgrade MySQL and I am trying to estimate how long the dump and subsequent import may take.

Resolution

DISCLAIMER:
This knowledgebase (KB) article is provided for informational purposes only and as a courtesy service to you, our customer. GWAVA Technical Support does not have any database administration (DBA) expertise, nor does it provide DBA services or support. GWAVA is not responsible for the results of implementing any of the concepts contained in this KB article. Implementation of any of the concepts suggested in this KB article shall be done entirely at your own and sole risk, and GWAVA does not provide any kind of warranties whatsoever resulting from your decision of implementing any of the KB article’s concepts. It is up to you to do any research and to ensure yourself that any implementation and setup of any of the KB article’s concepts in your database system is correctly and properly executed. It is imperative that you have backups of your database system and storage directory before making any implementation. If you don’t have any DBA expertise, you should consult with a DBA expert before any implementation of the KB article’s concepts.  Under no circumstances, shall GWAVA, or any of its employees, be liable, in contract, tort, delict or otherwise, whether negligence is provable or not, for any direct, indirect, incidental, special, punitive, consequential or other damages, loss, cost or liability whatsoever that would result from or are related to the implementation of any of the concepts suggested in the KB article.

To the extent permitted by applicable law, GWAVA shall not be liable to you for any special, consequential, direct, indirect or similar damages, including any loss of data, arising out from migrating any type of messages, attachments, database, metadata in your Retain system to another server and/or location.

Resolution:

This depends on many system factors as well as on the number of records in the key tables that are being migrated to the new schema.


There is one MySQL query that will quickly list the records for all Retain tables:

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'retain';

Here is a sample output. 


The information_schema cannot be counted on to be completely up to date, but it should provide a loose idea.  To get the most accurate count:
1.  Login to mysql.

2.  Type "use retain", which indicates that you want to query against the retain database.

3.  Type "show tables;", which will show you all the tables. Copy and paste the results into a text file.

4.  Query each table, one by one: "SELECT COUNT(*) FROM [table name];". Replace "[table name]" with the name of the table.

For example: SELECT COUNT(*) FROM t_message;

The main tables of concern are:

  • attachment
  • Document
  • Email
  • t_msg_properties
  • t_recp_properties
  • t_recipients

The tables t_msg_properties and t_recipients should be the largest tables.   From what this author has seen so far with customer migrations, a very (very) rough estimate would be to plan on about 24 hours for every 100 million records in t_msg_properties; yet, your system could be faster or slower, so this may not apply to you.

However, there are so many other factors involved.  To mention a few:

  • RAM.  MySQL likes to load tablespace into memory.  Reading/writing to memory is much faster than disk I/O.  However, experience has taught us that there do not appear to be visible gains after 10G allocated to MySQL.
  • Table fragmentation.
  • Disk I/O speed.
  • Whether you are going across a network.
  • CPU power.

For ideas on database maintenance, including pointers on MySQL memory configuration, see "MySQL Database Maintenance for Best Performance".

Additional Information

This article was originally published in the GWAVA knowledgebase as article ID 2235.