MySQL Performance: MySQL => InnoDB and COUNT(*)

Members

Help

Home

Home » Performance » MySQL » InnoDB and COUNT(*)

Show: Today's Messages :: Show Polls :: Message Navigator

InnoDB and COUNT(*) [message #153]

Thu, 07 September 2006 14:14

dinky
Messages: 4
Registered: September 2006
Location: Toronto, ON

Junior Member

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

Report message to a moderator

Re: InnoDB and COUNT(*) [message #154 is a reply to message #153 ]

Thu, 07 September 2006 14:30

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: InnoDB and COUNT(*) [message #155 is a reply to message #154 ]

Thu, 07 September 2006 14:36

dinky
Messages: 4
Registered: September 2006
Location: Toronto, ON

Junior Member

That's what I thought... even SELECT COUNT(primary_key_field) was no faster Sad

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

)

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

)
--
Dmitry

Report message to a moderator

Re: InnoDB and COUNT(*) [message #156 is a reply to message #155 ]

Thu, 07 September 2006 15:01

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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 Smile

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	MySQL vs MSSQL
Next Topic:	SQL_CALC_FOUND_ROWS

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 8 18:40:34 EST 2009

Total time taken to generate the page: 0.03041 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.