By default, phpMyAdmin shows an estimated number of records for InnoDB tables that have more than 20,000 rows. It can vary by every fresh as much as 50% or even more. Makes it hard to get an exact number of records for the tables as we have to explicitly run an SQL query to do that:
SELECT COUNT(*) FROM table_name
While it would make it a bit slower for phpMyAdmin to open up the database tables list page because it has to count the exact total number of records of each table in the database by this query, it’s worth it for some of us as we want to know the exact number of rows the InnoDB table currently has. phpMyAdmin does it for MyISAM tables, and we want it to do the same with InnoDB tables.
So how can we make phpMyAdmin show exact number of records for InnoDB tables?
Simple. Just open the config.inc.php in your phpMyAdmin installation directory and add this line:
$cfg['MaxExactCount'] = 2000000;
This configuration parameter sets the threshold that phpMyAdmin executes COUNT(*) against the table.
In this case, if the total number of recrods in this InnoDB table is larger than 2,000,000, the Rows count will be estimated, starting with a wave sign, ~; on the other hand, if the total number of records in this InnoDB table is smaller than 2,000,000, phpMyAdmin will run COUNT(*) against the table so an exact Rows count is displayed.
You get the idea.
To make phpMyAdmin show exact number of rows on all InnoDB tables, simply make the parameter $cfg[‘MaxExactCount’] large enough.
You should also read:
- MySQL: id BETWEEN start AND end Instead of LIMIT start, step For Better Database Performance
- MySQL: Get the exact size of a database by SQL query
- SQL: CREATE TABLE … SELECT … to generate new table from existing tables
- MySQL: Update Multiple Rows or Records with One Single Query
- MySQL, PHP: Display MySQL table fields and data