Hi again Darin!

First of all, I am surprised by your findings. I knew Firebird was a lot
better with AND than OR, but I thought that was due to how it could use
indexes.

Though I must admit that I find your test a bit like testing how good
Ronaldinho is to play football when you put cement around the football
and doesn't inflate it. He wouldn't be able to reach the goalline on a
penalty! Indexing is very important in Firebird, it isn't very useful
without. And even with your small tables, you do join on gender and if
they're about equally distributed, that should mean about 5-6 million
combinations to check. Going through that amount of data without being
able to use any index, I'd actually be satisfied by 17 seconds!

The one thing your test shows very efficiently, is that you should never
join tables on a field with that low selectivity as GENDER - especially
not when you use non-indexed tables and use OR in the where clause.

Firebird does have one further problem that you've not yet discovered -
it makes bad use of low selectivity indexes, typically foreign keys (I
think that is improved a lot in Firebird 2.0).

And by the way, PLANs (how indexes are used) can have great impact on
execution time. It is at least possible to do changes (create indexes or
modify the query) so that execution time goes from minutes to subsecond,
and I would expect it to be possible to change from hours to subsecond.

Set

Darin Amos wrote:

> I am actually not using any indexes, just the raw tables. This may seem
>strange for working with alot of data but the idea is to try and bring
>Firebird to it's knees. The time difference seems to be far to much

for it

>to be an issue with indexing( less than 1 second with AND and 12-17

seconds

>with OR).