Menu
I'm showing all rows (825) in phpMyAdmin v4.3.2 for a mysql innodb table sorted by the default autoincrement primary key. When sorted ascending, I go to the last page but it stops at id = 1150 when it should be id = 1337. The last 200 or so records do not display. When sorted descending, the records correctly start at the last record.
Oddly, increasing the number of rows per page give more of the missing rows and if I set it to 250 rows per page, they will all be there.
Any ideas why the result set is truncated? It seems as if phpMyAdmin is miscalculating number of pages.
UPDATE I upgraded to the latest version 4.5.0.2 and the problem persists.
UPDATE2
The query executed when entering the table is SELECT * FROM gems
The result: Showing rows 0 - 24 (825 total, Query took 0.0000 seconds.)
When I do the query select count(*) from gems the result returns 997. So the problem is clearly in the estimated number of records phpMyAdmin thinks is there.
494
Answer. This is due to phpMyAdmin using a faster, but inaccurate method of determining row counts of a table if it is over a certain number of rows. This defaults to 50,000 rows now for performance reasons per the phpMyAdmin documentation.
On the phpMyAdmin home screen click Settings >> Main frame >> Browse mode and altering the value within Maximum number of rows to display .
So as the answer there can be 1,073,741,824 rows. Save this answer.
Save this answer. Show activity on this post. Millions of rows is fine, tens of millions of rows is fine - provided you've got an even remotely decent server, i.e. a few Gbs of RAM, plenty disk space. You will need to learn about indexes for fast retrieval, but in terms of MySQL being able to handle it, no problem.
I found that I had the following setting in my phpMyAdmin config file pasted from who know what previous performance fix I made.
$cfg['MaxExactCount'] = 0
This disabled correcting InnoDB estimates. I commented out this line, and of course it took care of the problem
149
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