Phpmyadmin - Total Record Count Varies

PHPMyAdmin - Total record count varies

For InnoDB tables, from phpMyAdmin's FAQ:

phpMyAdmin uses a quick method to get
the row count, and this method only
returns an approximate count in the
case of InnoDB tables. See
$cfg['MaxExactCount'] for a way to
modify those results, but this could
have a serious impact on performance.

http://docs.phpmyadmin.net/en/latest/config.html?highlight=maxexactcount#cfg_MaxExactCount

Number of rows are different in MySQL show total and count query

When you browse using a tool, like phpmyadmin or heidiSQL, a query like this is executed:

SHOW TABLE STATUS LIKE 'table';

this value is inaccurate and if you run it many times, it always gives different results.

on the contrary the query:

select count(*) from table

is actually counting the records, and gives a correct result.

As mentioned in mysql documentation https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html:

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.

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

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”.

Make phpMyAdmin show exact number of records for InnoDB tables?

Just found the answer on this thread: PHPMyAdmin - Total record count varies

Seems adding a configuration parameter in config.inc.php does the trick: http://www.kavoir.com/2012/07/make-phpmyadmin-show-exact-number-of-records-for-innodb-tables.html

Just add this line in config.inc.php:

$cfg['MaxExactCount'] = 2000000;

So that any InnoDB table with less than 2000000 rows will have an exact number of rows displayed.

Make this number large enough and all InnoDB tables in your database will have an exact number of rows on phpMyAdmin database page.

phpmyadmin: Incorrect table rowcount with MySQL

This is a FAQ for InnoDB tables.
See the explanation at https://phpmyadmin.readthedocs.org/en/latest/faq.html?highlight=MaxExactCount#the-number-of-rows-for-innodb-tables-is-not-correct

SQL query showing different record count than PHPMyAdmin

did you try to 'distinct' the project_id ?

Select distinct project_id from projects;

How to get total number of rows in a MySQL database?

Use the schema:

SELECT SUM(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '{DB_NAME}'

That is what phpmyadmin is using.

Source: Get record counts for all tables in MySQL database

MySQL display total count of values under limit

You can use GROUP BY COUNT and HAVING

CREATE TABLE Table1
    (`CarID` int, `Car` varchar(4), `OwnerID` int)
;
    
INSERT INTO Table1
    (`CarID`, `Car`, `OwnerID`)
VALUES
    (1, 'Name', 1),
    (2, 'Name', 3),
    (3, 'Name', 2),
    (4, 'Name', 1)
;

SELECT `OwnerID`, COUNT(*) as countr
FROM Table1
GROUP BY `OwnerID`
HAVING countr < 2

OwnerID | countr
------: | -----:
      3 |      1
      2 |      1

db<>fiddle here

---

Related Topics