At 04:06 PM 20/10/2005 +1300, you wrote:

>Hi
>
>I know this is probably a bit off topic but...
>
>I never realised how many other DBs were implementing an MGA
>architecture. One of FBs weaknesses has been with Select Count()...
>statements. It has always been blamed on the fact that FB is MGA and
>'Other' faster DB is not. If the others are MGA as well is the select
>Count() really a FB issue NOT and MGA issue?

Back at you, to ask yourself why select count(*) is a "weakness" at
all? What on earth do you want it for? It has its possible uses in
aggregate queries, where it doesn't pose a performance problem, but what is
a sensible reason for counting all of the rows in a table? And, if you do
count them, which ones are you going to count? and why?

Other dbs - especially the structurally weaker ones like Sybase/MSSQL and
ISAM systems - may have *needed* counts to support implementation of the
IDENTITY type, or simply to meet demand from profligate users for a
brain-dead way to generate multi-user-unsafe keys. These dbs get quick
counts because either (or both) they violate relational rules by imposing
physical cardinality on stored relations ("record numbers") and/or they
store a record count in the table's header.

Now, when it comes to MGA/MVCC, what is this record count going to
represent? DBMSs that are in the process of superimposing MGA over the top
of architectures that pervasively rely on physical cardinality are likely
to be on a long QA trail to reconcile hard-wired record counts/hard-wired
cardinality with MGA. OK, Yukon has had five years so far to make it
work. Nobody yet knows whether it does, or whether it belabours the engine
the way Oracle's MVCC does. So has PG had it on the desk for five years,
albeit with volunteers (==greater demand on human resources, same situation
as Firebird!) and it's still working on it. As far as we can tell (not
open source) InnoDB hasn't (yet) attempted to inject it.

Helen