Skip to content

Understanding Rows Counting Before Export Process in MySqlBackup.NET

Jump to bottom
adriancs edited this page Jul 14, 2025 · 3 revisions

Understanding Rows Counting Before Export Process in MySqlBackup.NET

This is used for progress reporting.

There are two main approaches to get total rows in MySQL:

1. Using INFORMATION_SCHEMA.TABLES:

SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

2. Using SELECT COUNT(*):

SELECT COUNT(*) FROM tablename;

The key difference is that INFORMATION_SCHEMA.TABLES provides an approximate row count (especially for InnoDB tables) but is extremely fast since it uses cached values. On the other hand, SELECT COUNT(*) gives you the exact row count but can be significantly slower on large tables.

How MySqlBackup.NET Handles This

MySqlBackup.NET provides flexibility through the ExportInfo.GetTotalRowsMode property, which offers four options:

public enum GetTotalRowsMethod
{
    Auto = 0,
    Skip = 1,
    InformationSchema = 2,
    SelectCount = 3
}
  • Auto: When there is progress report events subscribed, it will Skip, else it will use SelectCount.
  • Skip: Do not perform rows count. Save time.
  • InformationSchema: Get the cached estimated/approximated total rows from INFORMATION_SCHEMA. This process is fast.
  • SelectCount: Manually count the total rows. Slightly slower than INFORMATION_SCHEMA, but it depends on a few factors (storage type, database size, etc). Performance vary, sometimes it could be unnoticeable if compares to SelectCount.

Example usage:

using(var conn = new MySqlConnection(constr))
using(var cmd = conn.CreateCommand())
using(var mb = new MySqlBackup(cmd))
{
    conn.Open();
    mb.ExportInfo.GetTotalRowsMode = GetTotalRowsMethod.SelectCount;
    mb.ExportToFile(sqlFile);
}

Version Changes and Defaults

  • Prior to v2.6: Default was GetTotalRowsMethod.InformationSchema for speed, but with approximate counts
  • Starting from v2.6.4: Default changed to GetTotalRowsMethod.Auto

Recommendation

For accurate progress reporting, it is recommended to explicitly set:

mb.ExportInfo.GetTotalRowsMode = GetTotalRowsMethod.SelectCount;

If you don't need progress reporting, you can improve performance by using:

mb.ExportInfo.GetTotalRowsMode = GetTotalRowsMethod.Skip;

Working Example

Here is a video demo showing accurate progress reporting in ASP.NET Web Forms:

Demo Video: https://www.youtube.com/watch?v=ToFVn9AgYMw

Source Code:

Tech Stack: ASP.NET Web Forms, MySQL, MySqlBackup.NET, pure HTML/CSS/JavaScript (no server controls, zero viewstate)

Cheers

Clone this wiki locally