>
> The reason this has come up for me is that I'm migrating from
> mysql to firebird (because I like the advanced features of
> firebird!), and these are queries that previously ran in
> acceptable time under mysql. I suppose that mysql does checks
> for naive queries and in general holds the hand of user.

Since it doesn't have multiple version of the same record, that would
not be an issue for it. It does not so much hold your hand as cross
its fingers that no-one deletes one of the records mid count :)

> Thanks
> for pointing out the solution to the "select count(*)" problem.
> I can already see an equivalent solution for the other queries.
> But doesn't writing all these trigger functions seem more
> complicated and error prone?
>

At first definately. I mean it is a simple operation right? But if
you consider it a bit longer, is it not more error prone to hope that
no-one changes a record you are trying to access. One of the things I
guarantee you will love about FB is that ACID can be achieved. It is
like any other logic you do. Sit down and ask yourself how it should
behave, write some test cases and do unit testing on it.

Select count(*) is one of the few cases where multi-version records
creates some form of ambiguity. With triggers, you can create
exceptions if someone tries for example to insert invalid
information. So at the database level, your data can not be invalid.
So it is a trade off, yes there is additional work in defining
triggers etc, and even this work risks introducing errors. BUT,
without them, you have to trust your developers (and even yourself)
to not stuff up in your application code and cause problems
everywhere. A lot of the problems we encountered in our previous
application were because Paradox didn't have triggers to enforce
consistency, and the UI was forced to handle invalid data.



Adam