Re: Indices for select count(*)?

On Thu, 2005-12-22 at 09:33, Jim C. Nasby wrote:
> On Thu, Dec 22, 2005 at 04:10:50PM +0100, Martijn van Oosterhout wrote:
> > Actually, ISTM the trend is going the other way. MySQL has instant
> > select count(*), as long as you're only using ISAM. Recent versions of
>
> No comment.
>
> > MSSQL use an MVCC type system and it also scans the whole table. Oracle
> > is the only one I've found that has any optimisation on this front.
>
> I think this is more an indication of the power of MVCC over traditional
> locking rather than the importance of indexes covering (reading just an
> index to satisfy a query). Index covering can be a huge benefit, and I'd
> be surprised if MS didn't come out with some way to do it in a future
> version. I'm actually a bit surprised they don't do it in SQL2005.

I wouldn't mind a "with visibility" switch for indexes that you could
throw when creating them for this purpose. But burdening all indexes
with this overhead when most wouldn't need it is not, IMHO, a good idea.

I seem to remember Tom saying that there was a race condition issue
though with updating the table AND the index at the same time, that they
could be out of sync for a fraction of a second or something like that.
So, if we had this kind of thing, the indexes and / or tables would have
to be locked for updates.

Again, for a reporting database, no big deal. For a transactional
database, very big deal.