MySQL gives fluctuating row count when I query schema?

Question

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 

Answer 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.)