SHOW TABLE STATUS show double the row count after OPTIMIZE TABLE

{question}

Why does SHOW TABLE STATUS display double the row count after OPTIMIZE TABLE?

{question}

{answer}

Summary:

When running OPTIMIZE TABLE ... FLUSH on a rowstore table in SingleStore, the SHOW TABLE STATUScommand may temporarily show a row count that is roughly double the actual number of rows. This article explains why this happens and whether it’s expected behavior.

Behavior:

After executing the following steps:

1. Creating a rowstore table and inserting 100,000 rows.

2. Running OPTIMIZE TABLE <table_name> FLUSH.

3. Immediately running SHOW TABLE STATUS.

You may observe that the Rows column shows approximately 200,000 rows instead of 100,000.

Explanation:

This behavior is expected during the optimization process; both the original and rewritten rows may temporarily coexist. If you run SHOW TABLE STATUS while the rewrite is still in progress, the system may count both sets of rows, resulting in an inflated row count.

This temporary state resolves on its own once the operation is complete and cleanup is finished. Waiting a few seconds before querying again typically reveals the correct number of rows.

Example:

  -- Insert 100,000 rows
  insert into tb_col select rn, lpad(rn, 10, '0') from gen_rows(100000);
  -- Run optimization
  OPTIMIZE TABLE tb_col FLUSH;
  -- Query table status immediately
  SHOW TABLE STATUS LIKE 'tb_col';
  -- Rows: 200000 (temporarily)
  -- Query table status after a few seconds
  SHOW TABLE STATUS LIKE 'tb_col';
  -- Rows: 100000 (corrected)

Key Points:

• The behavior is not a bug.

• It’s due to the internal batching mechanism of OPTIMIZE TABLE.

• The row count displayed by SHOW TABLE STATUS will update itself once the process completes.

Further Reading:

• OPTIMIZE TABLE Documentation

{answer}