Difference count of row table at information_scheme

adyoi posted 3 weeks ago in General

information_scheme difference row when im counting the table with

select count(id) from mytable


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 (64 Bit)

adyoi posted 3 weeks ago

Database Version :


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

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

Misha v.3 posted 2 weeks ago

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


"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 2 weeks 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.