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

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

Have you had a look at this: https://stackoverflow.com/questions/11926259/why-is-the-estimated-rows-count-very-different-in-phpmyadmin-results — Taher A. Ghaleb, Nov 20 '18 at 15:59

Aris · Accepted Answer · 2019-05-06T12:43:32.170

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.

score 0 · Answer 2 · answered Nov 20 '18 at 15:58

0

Mysql said: by INNODb the total is just approximation of the count.

https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html

answered Nov 20 '18 at 15:58

László Tóth

483
5
15

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

2 Answers2