--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> Unless MEDIUM is a huge table, this seems OK to me (though I find it

strange that you use the reserved word POSITION as a field name if
this is a new table, although there's nothing inherently wrong in
doing so). However, I am a bit puzzled why you use

>
> WHERE (S1.WORDID = (SELECT WORDID FROM FTS$WORD WHERE WORD = 'SINGIN')
> AND S1.FIELD_NAME = 'TRACK') AND ...

It was started a while ago and I wasn't in the habit of writing notes
at the time so their a few things I do that I'm not sure about, but
performance has been ok so I've been loathe to change anything.

MEDIUM is tiny (28 records at the moment) and I don't have any plans
on adding much to it.

> I also notice that you redundantly check PRODUCTID in the WHERE

clause (you've already said that it has to be the same in your JOIN
clause).

>
> and would myself first have tried this sql:

I think the plan for that is better (which is to say there are no
natural joins) but the query takes about 2 minutes to run and has
~700k reads on fts$score so I might just clean up the original SQL.

Thanks very much