Re: count * performance issue

On Thu, 6 Mar 2008, Steinar H. Gunderson wrote:

> On Thu, Mar 06, 2008 at 07:28:50AM -0800, Craig James wrote:
>> Count() on Oracle and MySQL is almost instantaneous, even for very large
>> tables. So why can't Postgres do what they do?
>
> In MySQL's case: Handle transactions. (Try COUNT(*) on an InnoDB table.)

Exactly.  There is a good discussion of this at
http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/ and I
found the comments from Ken Jacobs were the most informative.

In short, if you want any reasonable database integrity you have to use
InnoDB with MySQL, and once you make that choice it has the same problem.
You only get this accelerated significantly when using MyISAM, which can
tell you an exact count of all the rows it hasn't corrupted yet.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD