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

Question

I'm getting confused with the number of rows in MySQL. The total rows returned by phpMyAdmin and the count query are different.

My table structure is InnoDB.

Showing rows 0 - 24 (655537 total, Query took 0.1527 seconds.)

This is result of When browse the Table.

My count query was

SELECT count(*) FROM `table_name`

This will be return as 602030

Answer 1

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.