Announcement Module
No announcement yet.

InnoDB and COUNT(*)

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • InnoDB and COUNT(*)

    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!

  • #2
    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.


    • #3
      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)? ))


      • #4
        Right triggers is yet another workaround even thought not very convenient.

        Yes this is major bummer for some of the customers, especially moving from MyISAM and I constantly bug Heikki to fix it )