> > Make a new field in the table which is just a calculated fields
> > ((MyField1*100)+MyField2) and then make an index on this new field ?
> Yes.

The main tradeoffs in this issue for me is choosing between Data
Integrity and Query Speed. For example, the above field should not be a
physical data field, probably better implemented as a COMPUTED FIELD
instead, so we don't have to maintain triggers/etc to make sure that the
field is valid.

On the other hand, COMPUTED FIELD cannot not indexed (AFAIK), so a query
based on this calculated field will be slower (if not very slow..;),
probably involving a full table scan, which could kill performance if
the computed field's calculation is complex or when we have a big
dataset.

What should one do in this situation?

TIA,
sugi.

PS:
I don't know much about FB's internals, but wouldn't it be great if
firebird could somehow keeping a cache of 'computed field's (maintained
behind the screen on updates, etc) so the values can be indexed (also
behind the screen) and used by the optimizer if necessary. This way