# InnoDB and COUNT(\*)

**URL:** <https://forums.percona.com/t/innodb-and-count/78>\
**Category:** Other MySQL® Questions\
**Created:** [September 7, 2006, 12:44pm UTC](https://forums.percona.com/t/innodb-and-count/78 "2006-09-07T12:44:17Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dinky](https://avatars.discourse-cdn.com/v4/letter/d/22d042/32.png) [@dinky](https://forums.percona.com/u/dinky)\
**Post date:** [September 7, 2006, 12:44pm UTC](https://forums.percona.com/t/innodb-and-count/78/1 "2006-09-07T12:44:17Z")

</div>

It is probably well-known feature/bug but still is a somewhat mystery for me - why exactly a simple “SELECT COUNT(\*) FROM table” can take hours to complete on a relatively small dataset (~50M records), not depending on isolation level, no different in 4/4.1/5 ?

## Thank you!

Dmitry

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [September 7, 2006, 1:00pm UTC](https://forums.percona.com/t/innodb-and-count/78/2 "2006-09-07T13:00:45Z")

</div>

Innodb does not store count(\*) counter as MyISAM does, this is because it is multi version system and each transaction could have different number of rows visible.

So SELECT COUNT(\*) is executed as full table scan or index scan.  
If your table is fragmented (physically) it may take quite a while.

SHOW TABLE STATUS LIKE ‘table’ provides approximate row counter which can be used for some applications. There also other workarounds.

---

<div class="post-metadata">

**Author:** ![dinky](https://avatars.discourse-cdn.com/v4/letter/d/22d042/32.png) [@dinky](https://forums.percona.com/u/dinky)\
**Post date:** [September 7, 2006, 1:06pm UTC](https://forums.percona.com/t/innodb-and-count/78/3 "2006-09-07T13:06:46Z")

</div>

That’s what I thought… even SELECT COUNT(primary\_key\_field) was no faster (

Yeah, my last idea was to create a table with list of tables and a bunch of triggers to keep actual row counts ))

## Just curious - haven’t you had clients who had the same problem? Weren’t they surprised (not in a good way)? ))

Dmitry