MySQL Schema Inspector Reporting Wrong Row Counts

When you use reports in MySQL, you expect them to be accurate. Let’s take, for example, the MySQL Schema Inspector. When you use the schema inspector, you point it at a schema. It gives you all sorts of useful information about that schema. A report I find quite helpful when comparing schemas against each other is the table report. This shows, in a grid, the tablenames, engine, version, row format, rows, and so on. You can see at a glance if the tables all match up between schemas.

However, the MySQL schema inspector reports the wrong row counts. It can appear that rows are either missing or extra.

So then you run some SQL commands to look for which rows might be extra in one table or the other. You compare user IDs or whatever else you can. But the reports show that nothing is extra in one table or the other. They seem similar.

So then you run actual count(*) commands manually on each table.

THE TABLES MATCH EXACTLY. Even more strange, that exact number of rows in the two tables does not match EITHER schema report for that table.

WHY????????

It turns out that the Schema Inspector value for an InnoDB table IS JUST AN ESTIMATE.

What?? The table is right there. You’re using a schema inspector against it. Surely it can tell you how many rows are there? And, sure, if the table is actively being used, it might not be able to count to account for rows in the process of being added and deleted. That makes sense.

But I’m talking about static rows here. Rows in a table which hasn’t been modified in months. The schema inspector can’t even get that table’s row count correct.

This can cause all sorts of trouble if you’re relying on the schema inspector to compare two schemas. In essence, you can’t rely on it at all. You have to manually run counts, table by table, to see if both have the same number of rows.

A royal pain, when you have a tool which supposedly does that task right in front of your eyes.

So be aware, if you use the Schema Inspector in MySQL, of this fairly serious limitation. My advice is to not even bother. Just start plunking away with your count(*) scripts and see for yourself the rows in the tables. It takes longer, but it gives you accurate results.