Why does my InnoDB table have a weird value for record count?

Question

Below is the ammount of rows for 2 tables in my mysql DB
1 is myisam the other innodb,

can someone tell me why the innodb one has this ~ in front of the number?

These numbers came from phpmyadmin

10,308      MyISAM 
~118,011    InnoDB

Answer 1

phpMyAdmin uses SHOW TABLE STATUS to get information for your tables.

From the documentation:

Rows

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

This is due to InnoDB being an ACID compliant storage engine. InnoDB implements MVCC using row-level locking. In short, there can be multiple copies of a given row at a given time. I suggest reading this article: Understanding InnoDB MVCC.

Answer 2

Because InnoDB is a transactional storage engine, the exact count is subject to interpretation. For example, some rows may exist in storage but the transaction that created those rows has not committed yet. So the rows should not be included in the count.

Even more confusing, your transaction may be REPEATABLE READ isolation level, which means only rows that were committed before your transaction began are visible. Some rows may be committed, but more recently than the start of your repeatable read transaction. So those rows also should not be included in the count. But the same rows are included in a count done by another transaction that was started later, or in READ COMMITTED isolation level.

This is why this table statistic for InnoDB can only be approximated. An accurate count requires a scan of all stored rows, to see if they are visible to the current transaction.