Geek Answers Handbook

Why is my InnoDB table weird for the number of records?

Below is the row count for 2 tables in my mysql DB
1 is myisam another innodb,

can someone tell me why innodb has this ~ before number?

These numbers come from phpmyadmin

10,308 MyISAM ~118,011 InnoDB
+4
source share
2 answers

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

In the documentation:

Line

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

This is because InnoDB is an ACID compatible storage engine. InnoDB implements MVCC with row-level locking . In short, there may be several copies of a given string at a given time. I suggest reading this article: Understanding InnoDB MVCC .

+11
source

Since InnoDB is a transactional storage engine, the exact score can be interpreted. For example, some rows may exist in the repository, but the transaction that created these rows has not yet completed. Therefore, lines should not be included in the bill.

Even more confusing, your transaction may be a REPEATABLE READ isolation level, which means that only rows that were committed before the transaction started are visible. Some lines may be committed, but more recently than the start of a repeat read transaction. Therefore, these lines should also not be included in the bill. But the same lines are included in the account made by another transaction that was launched later, or in the isolation level READ COMMITTED .

That's why this statistics table for InnoDB can only be approximate. Exact counting requires checking all stored rows to see if they are visible to the current transaction.

+4
source

More articles:


All Articles