Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » InnoDB and COUNT(*)
icon9.gif  InnoDB and COUNT(*) [message #153] Thu, 07 September 2006 14:14 Go to next message
dinky is currently offline 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 Go to previous messageGo to next message
Peter is currently offline 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 Go to previous messageGo to next message
dinky is currently offline 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 Go to previous message
Peter is currently offline 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:
  

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

Current Time: Sat Jul 4 17:02:58 EDT 2009

Total time taken to generate the page: 0.01094 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.