Why is the estimated rows count very different in phpmyadmin results?

by

Unlike MyISAM tables, InnoDB tables don’t keep track of how many rows the table contains.

Because of this, the only way to know the exact number of rows in an InnoDB table is to inspect each row in the table and accumulate a count. Therefore, on large InnoDB tables, performing a SELECT COUNT(*) FROM innodb_table query can be very slow because it does a full table scan to get the number of rows.

phpMyAdmin uses a query like SHOW TABLE STATUS to get an estimated count of the number of rows in the table from the engine (InnoDB). Since it’s just an estimate, it varies each time you call it, sometimes the variations can be fairly large, and sometimes they are close.

Here is an informative blog post about COUNT(*) for InnoDB tables by Percona.

The MySQL manual page for SHOW TABLE STATUS states:

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.

The page on InnoDB restrictions goes into some more detail:

SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.

InnoDB does not keep an internal count of rows in a table because
concurrent transactions might “see” different numbers of rows at the
same time. To process a SELECT COUNT(*) FROM t statement, InnoDB scans
an index of the table, which takes some time if the index is not
entirely in the buffer pool. If your table does not change often,
using the MySQL query cache is a good solution. To get a fast count,
you have to use a counter table you create yourself and let your
application update it according to the inserts and deletes it does. If
an approximate row count is sufficient, SHOW TABLE STATUS can be used.
See Section 14.3.14.1, “InnoDB Performance Tuning Tips”.

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. If two columns are 1 then make new column
  3. MySQL: How to reset or change the MySQL root password?
  4. MySQL Server has gone away when importing large sql file
  5. How to display UTF-8 characters in phpMyAdmin?
  6. #1071 – Specified key was too long; max key length is 1000 bytes
  7. Restore table structure from frm and ibd files
  8. Subqueries with EXISTS vs IN – MySQL
  9. phpMyAdmin on MySQL 8.0 [duplicate]
  10. Find and Replace text in the entire table using a MySQL query
  11. Setting up foreign keys in phpMyAdmin?
  12. How to access remote server with local phpMyAdmin client?
  13. Is there a simple way to convert MySQL data into Title Case?
  14. Regaining access to lost MySQL password for PHPMyAdmin on WAMP
  15. Bug? #1146 – Table ‘xxx.xxxxx’ doesn’t exist
  16. Creating functions in phpMyAdmin – Error: access denied you need the super privilege for this operation
  17. #1273 – Unknown collation: ‘utf8mb4_unicode_ci’ cPanel
  18. phpMyAdmin is throwing a #2002 cannot log in to the mysql server phpmyadmin
  19. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  20. Composite key as foreign key (sql)
  21. how to delete duplicate rows from a table in mysql
  22. Can’t import database through phpmyadmin file size too large
  23. #1045 – Access denied for user ‘root’@’localhost’ (using password: YES)
  24. Access denied after setting user’s password with SHA256 in phpMyAdmin
  25. SQL: deleting tables with prefix
  26. where does MySQL store database files?
  27. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server
  28. How to import large sql file in phpmyadmin
  29. Import CSV to Update rows in table
  30. mySQL query to search all tables within a database for a string?

Leave a Comment