Menu
Without any changes on database I got very different rows count on my tables.
What can cause this?
Server version: 5.1.63-cll
Engine: InnoDB
505
To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.
The affected_rows / mysqli_affected_rows() function returns the number of affected rows in the previous SELECT, INSERT, UPDATE, REPLACE, or DELETE query.
So as the answer there can be 1,073,741,824 rows.
@@rowcount is sql function that returns how many rows affected by query execution. we can also use it when insert data into two tables in single procedures based on first table data insertion. @@rowcount is sql function that returns how many rows affected by query execution.
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 tstatement, 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 STATUScan be used. See Section 14.3.14.1, “InnoDB Performance Tuning Tips”.
149
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.
8
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
