Re: Poor select count(*) performance

On Mon, Feb 23, 2009 at 11:21:16PM -0800, Mike Ivanov wrote:
> On Mon, Feb 23, 2009 at 6:54 PM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> > Depending on where these are on disk and how fast your disks are this
> > could take up to 30 seconds.
>
> This does not sound very inspiring :-)

It was calculated with a pessimistic seek time of 10ms * 3000 seeks.
Real worst case would be even worse as you'd have to factor in potential
misses of the index as well but that's unlikely. In practice, a table
is likely to be stored close together on the disk and hence assuming
average seek time is not accurate. If it's having to go off and read
the index then you may loose this spacial clustering and performance
will suffer.

> Would throwing more hardware (memory, faster CPU) at the server improve the
> situation?

You're IO bound not CPU bound; faster disks would help or if your
dataset's small enough more memory.

--
Sam http://samason.me.uk/