Table row count wrong after MySQL restore? Fix it here

You restored a backup, and now table row counts are all wrong. I've seen this drive people crazy.

The database seems fine. Queries work. Data is there. But SHOW TABLE STATUS shows 0 rows, or some random number that doesn't match reality. Sometimes tools like phpMyAdmin or admin panels show incorrect counts too.

Don't re-restore. Don't rebuild indexes. The fix is one command.

The fix: ANALYZE TABLE

MySQL and MariaDB don't update table statistics automatically after a restore, especially with InnoDB. The stored row count in information_schema.TABLES is just an estimate, and after a restore it's often stale or zero.

Here's what you do:

1. Connect to MySQL as a user with ALTER privilege on the database:

mysql -u root -p your_database_name

You'll be prompted for your MySQL root password. If you're using a different user, replace root with that username.

2. Run ANALYZE for a single table (replace your_table_name with the actual table name):

ANALYZE TABLE your_table_name;

After running that, you should see output like:

+-----------------------------+---------+----------+----------+
| Table                       | Op      | Msg_type | Msg_text |
+-----------------------------+---------+----------+----------+
| your_database.your_table    | analyze | status   | OK       |
+-----------------------------+---------+----------+----------+

Now check the row count again with:

SHOW TABLE STATUS LIKE 'your_table_name';

The Rows column should now show the correct count.

3. Fix all tables at once — run this from the MySQL prompt:

SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') AS stmt
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND ENGINE IS NOT NULL;

That generates ANALYZE commands for every table. Copy the output lines and run them one by one. Or you can do this in one go with a stored procedure — but for most people, copying and pasting works fine.

After that, run SHOW TABLE STATUS again. All row counts should match what you see with SELECT COUNT(*) FROM each table.

Why this happens

InnoDB stores table statistics as estimates, not exact numbers. When you restore a backup, those estimates are not recalculated. They stay at whatever value was in the backup file, which could be zero (if the backup tool didn't capture them) or some outdated number.

MySQL uses these statistics for query optimization. A bad row count can slow down queries that use JOINs or indexes. So fixing it isn't just about making phpMyAdmin look right — it can actually improve performance.

ANALYZE TABLE reads a random sample of rows (about 20 pages per table by default) and recalculates the estimate. It's fast, even on large tables. I've run it on 50GB tables and it finished in under a minute.

Less common variations

1. The count is still wrong after ANALYZE

Sometimes ANALYZE gives a slightly different number than SELECT COUNT(*). That's normal — InnoDB uses sampling, not full scans. If the difference is small (under 5%), don't worry. If it's huge, try:

OPTIMIZE TABLE your_table_name;

That rebuilds the table and recalculates statistics more thoroughly. It locks the table while running, so do it during low traffic.

2. Using MariaDB

MariaDB handles InnoDB statistics slightly differently. The fix is the same — ANALYZE TABLE still works. But if you're using Aria or MyISAM tables, you might need to run CHECK TABLE first, then ANALYZE. MyISAM stores exact row counts, so if those are wrong, the table might be corrupted.

3. Row count shows 0 for all tables after a partial restore

If you restored only certain tables (like with mysql -u root db_name < partial_backup.sql), the information_schema might not get updated for the missing tables. Run ANALYZE TABLE on the tables you restored. The unreferenced tables will still show 0 until they're accessed.

4. Using Percona XtraBackup or mysqldump

These tools often preserve table statistics in the backup file. But if you restore to a different MySQL version or server, the statistics might not apply. Always run ANALYZE TABLE after any cross-version restore.

How to prevent this problem

There's no way to force MySQL to recalculate statistics on restore automatically. But you can build it into your restore script. Here's a quick bash example:

#!/bin/bash
DB_NAME="your_database"
mysql -u root -p"$MYSQL_ROOT_PASSWORD" "$DB_NAME" < backup.sql
mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "
  SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = '$DB_NAME' AND ENGINE IS NOT NULL;
" | tail -n +2 | while read stmt; do
  mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "$stmt"
done

That restores the backup and then analyzes every table. Save it as restore_with_analyze.sh and run it anytime you need to restore.

Also, after any restore, always check that row counts look reasonable before pointing your application at the database. A quick SHOW TABLE STATUS takes two seconds and can save hours of debugging later.

One more thing: if you're using an older MySQL version (5.6 or earlier), the information_schema.TABLES.ROWS column can be wildly inaccurate even without a restore. Upgrading to 5.7 or 8.0 helps, but the same fix applies.

That's it. Run ANALYZE TABLE, move on with your day.