Re: Why count(*) doest use index?

On Sun, Mar 06, 2011 at 11:03:23AM +0300, Allan Kamau wrote:
> I would assume the primary key or unique indexes are the cornerstone
> of each insertion and deletion.

<snip>

> Or am I missing a crucial point.

The real issue is that you can have four programs all doing count(*)
and all getting different answers. How? Because what you see is
dependant on what snapshot of the database you're looking at. And
information about what snapshot can see what tuple is stored in the
table. An index does not have enough information to work this out.

The DBs that don't have this issue are usually like MyISAM, no
transactions so no issues about different snapshots. And crappy
concurrency. As soon as you go to more advanced systems the easy option
falls away. For example

http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/

If it's really really important there are ways you can use trigger
tables and summary views to achieve the results you want. Except it's
expensive and when people are told that all of the sudden the count(*)
performance isn't so important any more. :)

The other option is visibility data in the index. Doubles the size of
your indexes though.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
> - Charles de Gaulle