Difference count of row table at information_scheme

information_scheme difference row when im counting the table with

select count(id) from mytable

and

SELECT engine AS Engine, table_name AS Table, table_rows AS Rows, round(((data_length + index_length) / 1024 / 1024), 2) as Size
FROM information_schema.TABLES
WHERE table_schema = mytable ORDER BY Size desc;

HeidiSql version 9.5.0.5282 (64 Bit)

Database Version :

10.1.33-MariaDB

1. What is your table engine?
2. 'id' column -- is it declared as null or not null?

my table engine is innodb and id is primary key, thanks @misha

For innodb the row number requested from information_schema is estimation only:

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

"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)"

Use count() to get exact number. You should remember that count() from innodb without where clause would do full table scan and so they are slow for large tables.

okey, thanks before. my solution is this must be processed on looping after I get the table name and do SELECT COUNT(*) AS row FROM (get_the_table_from_information_schema)

well thanks @Misha