Re: Why Select Count(*) from table - took over 20 minutes?

In response to Ozz Nixon <ozznixon@gmail.com>:

> I am the only user on this system right now, and one table select count(*) took over 20 minutes:
>
> wikitags exists and has 58,988,656 records.
>
> Structure (in pascal) is:
>
>    quer.SQL.Add('create table '+DBTags+' (');
>    quer.SQL.Add('   pagename '+SQL_TITLE+'(100) not null,');
>    quer.SQL.Add('   tagword '+SQL_TITLE+'(15) not null,');
>    quer.SQL.Add('   soundex2 '+SQL_TITLE+'(4) not null,');
>    quer.SQL.Add('   metaphone '+SQL_TITLE+'(15) not null,');
>    quer.SQL.Add('   metaphone2 '+SQL_TITLE+'(22) not null,');
>    quer.SQL.Add('   carverphone '+SQL_TITLE+'(22) not null,');
>    quer.SQL.Add('   instances '+SQL_INT32+' not null,');
>    if SQL_NAME_PRIMARY_KEYS then quer.SQL.Add('   constraint '+DBTags+'_PK');
>    quer.SQL.Add('   primary key(pagename, tagword, instances)');
>    quer.SQL.Add(')');
>
> where SQL_TITLE = 'varchar', SQL_IN32 = 'int'
>
> I have hung off indexes for each column, to resolve my previous "performance" issue from 3+ weeks ago. However,
COUNT()is still dog slow - this table is a write once, read many... *never* update, nor delete. 
>
> Any suggestions?

Generate the count one time and store it somewhere for quick retrieval.

In an MVCC database, count(*) is designed to be accurate, which requires
a scan of the entire table (which appears to take about 20 mins on your
hardware).

MVCC just isn't optimized for a table that never changes.  However, it's
easy to cache that value, since it never changes the cache never needs
to be updated.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/