Hi
We're seeing terrible performance on the MAC (compared to the same
query under windows), Eg a 0.6 sec query on windows taking approx 5
seconds on the MAC (OSX).
It occurred to us that it may be classic server, since we'd heard it
was slower (classic server was the only recent binary available for
the MAC). A workmate downloaded the 1.5.1 source today, compiled
super-server for the MAC, and it does seem better, but probably only
by 1 second.

Our query is simple (two tables referenced by a cross-reference table):

select a.title from TableA a join CrossRef x on x.pkA = a.pkA join
TableB b on b.pkB = x.pkB where b.keyword like 'magic%'

There are 18,000 keywords in TableB, and selecting keywords starting
with 'magic' is fast both in windows and on the MAC. Similarly
performing the above query but without the where clause is fast on
both platforms. Using the entire query, it is still fast (0.6 seconds)
on windows, but on the MAC it's suddenly taking 5 seconds. That's a
huge drop in performance.

Then this afternoon I found something else weird.

I replaced "select a.title from TableA..." with "select * from
TableA..." and the query only takes 1.5 - 2.0 seconds!! That's a huge
improvement, but it doesn't make sense. How could selecting all
columns from a table be faster than just selecting one of them?


These findings suggest to me that the query optimisation on the MAC
has some issues.
a) Is it just that the MAC build process is pulling in some outdated
query optimisation code? There was no recent super-server binary for
the MAC, so I was wondering how often it gets worked on.
b) Is there an option to allow me to see the query plan that the
database is using?
c) Adding an index to TableB.keyword did not help and I gather with
text searches involving wildcards that this is the case for most
databases. I've been told however that SQL server supports a 'full
text index' that makes this type of search much faster - does firebird
support something similar?


Thanks
Phil