MySQL gives fluctuating row count when I query schema?

Here I am pressing up and running the same command on my dev DB on my laptop, over and over;

mysql> select count(*) from tblTraceOutput;
+----------+
| count(*) |
+----------+
|   300175 |
+----------+
1 row in set (0.42 sec)

mysql> select count(*) from tblTraceOutput;
+----------+
| count(*) |
+----------+
|   300175 |
+----------+
1 row in set (0.35 sec)

mysql> select count(*) from tblTraceOutput;
+----------+
| count(*) |
+----------+
|   300175 |
+----------+
1 row in set (0.45 sec)

Here I am doing the same, pressing 'up' and running the last command again, but the output is chaning. What is going on here? Nothing is using this database as it's a copy on my local laptop for my own tinkering. Why is the table row count changing for table tblTraceOutput?

mysql> SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'smoketrace';
+----------------+------------+
| table_name     | table_rows |
+----------------+------------+
| tblCategories  |          9 |
| tblResults     |      32463 |
| tblRoutes      |        300 |
| tblSettings    |          2 |
| tblTraceOutput |     303463 |
| tblTraces      |         12 |
+----------------+------------+
6 rows in set (0.01 sec)

mysql> SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'smoketrace';
+----------------+------------+
| table_name     | table_rows |
+----------------+------------+
| tblCategories  |          9 |
| tblResults     |      32948 |
| tblRoutes      |        246 |
| tblSettings    |          2 |
| tblTraceOutput |     297319 |
| tblTraces      |         12 |
+----------------+------------+
6 rows in set (0.00 sec)

mysql> SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'smoketrace';
+----------------+------------+
| table_name     | table_rows |
+----------------+------------+
| tblCategories  |          9 |
| tblResults     |      32948 |
| tblRoutes      |        451 |
| tblSettings    |          2 |
| tblTraceOutput |     302127 |
| tblTraces      |         12 |
+----------------+------------+
6 rows in set (0.02 sec)

I was seeing this behaviour in phpMyAdmin when refreshing the page, so I wanted to check for myself on the CLI and as you can see, it really is changing!

mysql --version
./bin/mysql  Ver 14.14 Distrib 5.5.8, for Linux (i686) using  EditLine wrapper
free -m
             total       used       free     shared    buffers     cached
Mem:          1880       1830         49          0         51        600
-/+ buffers/cache:       1179        701
Swap:         1027          0       1026
uname -a
Linux laptop 3.4.11 #1 SMP Sun Sep 23 15:03:21 BST 2012 i686 i686 i386 GNU/Linux

Solution 1:

Assuming you are using InnoDB, as that is the default in 5.5.x according to the MySQL INFORMATION_SCHEMA TABLES documentation.

And this note:

The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

Solution 2:

It's not a bug. The value reported in the table_rows column of information_schema.tables is not guaranteed to be exact, nor do we expect it to be.

Solution 3:

If you're using InnoDB as the storage engine, the number of rows is an estimate.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. 

Source

Solution 4:

It seems you are using an InnoDB table. They only hold a very rough estimate of row numbers in the status table meant to help the MySQL optimizer have basis for the query plan choices. For an exact row count you should keep a separate counter if needed frequently (as select count(*) is far from efficient).