Difference count of row table at information_scheme

adyoi posted 4 months ago in General

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)

adyoi posted 4 months ago

Database Version :

10.1.33-MariaDB

Misha v.3 posted 4 months ago
  1. What is your table engine?
  2. 'id' column -- is it declared as null or not null?
adyoi posted 4 months ago

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

Misha v.3 posted 4 months ago

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.

adyoi posted 4 months ago

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

Please login to leave a reply, or register at first.