Result table rows growing

Message boards : Closed Issues : Result table rows growing
Message board moderation

To post messages, you must log in.

AuthorMessage
Tomáš Brada
Project administrator
Volunteer developer
Avatar

Send message
Joined: 3 Feb 19
Posts: 505
Credit: 407,379
RAC: 525
Message 3752 - Posted: 4 Nov 2019, 14:36:05 UTC

There are 5'383'613 rows in "result" table according to dbinfo.php, which is about 5 times as much as I would expect. (Old/processed results are deleted by my program automatically.)
However simple count query over the table reports only 236'865 rows.
What is going on?
ID: 3752 · Rating: 0 · rate: Rate + / Rate - Report as offensive     Reply Quote
Profile Michael Goetz
Avatar

Send message
Joined: 18 Feb 19
Posts: 22
Credit: 147,831
RAC: 861
Message 3755 - Posted: 4 Nov 2019, 17:57:15 UTC - in response to Message 3752.  
Last modified: 4 Nov 2019, 17:57:45 UTC

There are 5'383'613 rows in "result" table according to dbinfo.php, which is about 5 times as much as I would expect. (Old/processed results are deleted by my program automatically.)
However simple count query over the table reports only 236'865 rows.
What is going on?


tl;dr: Believe what count(*) tells you rather than dbinfo.php.

ops/dbinfo.php uses the SHOW TABLE STATUS command rather than SELECT COUNT(*).

From the mysql documentation for SHOW TABLE STATUS:

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.

The Rows value is NULL for INFORMATION_SCHEMA tables.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)


I get some odd row counts on my system too, but not as bizarre as yours. I guess you're special! :)

I wouldn't worry about it.
Want to find one of the largest known primes? Try PrimeGrid. Or help cure disease at WCG.

ID: 3755 · Rating: 0 · rate: Rate + / Rate - Report as offensive     Reply Quote
Tomáš Brada
Project administrator
Volunteer developer
Avatar

Send message
Joined: 3 Feb 19
Posts: 505
Credit: 407,379
RAC: 525
Message 3757 - Posted: 4 Nov 2019, 19:06:15 UTC

After running ANALYZE on the results table, the estimated row count dropped to around 4 millions. The same value is shown as primary index cardinality in phpmyadmin. Mysql documentation confirms that the index cardinality is only approximate. It is interesting that the estimate is so massively off.
Anyway, the count() is right and I do not have such crazy number of rows. If it was that high, it would mean I have error in the cleaner program.
I do not need to worry about this more.
ID: 3757 · Rating: 0 · rate: Rate + / Rate - Report as offensive     Reply Quote

Message boards : Closed Issues : Result table rows growing

©2020 Tomáš Brada