Hi Tom!

With Arnos second solution, you will not need (c.contid>0), well, if
that one is there for the optimizer to choose the plan you want rather
than actually checking for a positive id.

As for the +0 for the joins, it is sometimes needed (though read on,
there are other ways to write something similar). Your query indicates
that contid and skillid have similar selectivity (or that you have no
index for skillid), though my logic would expect contid to be far more
selective than skillid.

Hence, this is the query I would have tried in Firebird 1.5 based on
Arnos second suggestion (I don't know enough about your data to tell
whether his solution #1 or #2 is the best:

SELECT
c.contid, c.surname, c.forename, c.dob,
c.postcode, c.filed, c.available, c.email
FROM
cont c
WHERE
EXISTS(
SELECT 1 FROM
contskill t1
JOIN contskill t2 ON t2.contid = c.contid
AND (2=0 or t2.skillid = 17)
JOIN contskill t3 ON t3.contid = c.contid
AND (2=0 OR t3.skillid = 18)
JOIN contskill t4 ON t4.contid = c.contid
AND (2=0 OR t4.skillid = 22)
JOIN contskill t5 ON t5.contid = c.contid
AND (2=0 OR t5.skillid = 24)
JOIN contskill t6 ON t6.contid = c.contid
AND (2=0 OR t6.skillid = 29)
JOIN contskill t7 ON t7.contid = c.contid
AND (2=0 OR t7.skillid = 30)
JOIN contskill t8 ON t8.contid = c.contid
AND (2=0 OR t8.skillid = 34)
JOIN contskill t9 ON t9.contid = c.contid
AND (2=0 OR t9.skillid = 4209)
JOIN contskill t10 ON t10.contid = c.contid
AND (2=0 OR t10.skillid = 62)
WHERE
t1.contid = c.contid AND (2=0 OR t1.skillid = 10))

I used 2=0 (whatever evaluates to false) to make it possible to add a
random number of conditions after the 2=0 knowing that none of them will
use any index. It is just an alternative to +0 that may cover more than
one comparison.

Your real generated SQL, I would consider changing to

SELECT DISTINCT c.contid, c.surname, c.forename, c.dob, c.postcode,
c.filed, c.available, c.email
FROM cont c
JOIN contskill t1 ON c.contid=t1.contid
AND t1.skillid=29
JOIN contskill t2 ON c.contid=t2.contid
AND (2=0 OR t2.skillid=34 )
JOIN contskill t3 ON c.contid=t3.contid
AND (2=0 OR t3.skillid=247 )
JOIN contskill t4 ON c.contid=t4.contid
AND (2=0 OR t4.skillid=256 )
JOIN contskill t5 ON c.contid=t5.contid
AND (2=0 OR t5.skillid=321 )
JOIN contskill t6 ON c.contid=t6.contid
AND (2=0 OR t6.skillid=3024 )
JOIN contskill t7 ON c.contid=t7.contid
AND (2=0
OR t7.skillid IN (118,136,784,1789,2184,2393)
OR (t7.skillid=1402
AND t7.rating=1 ))
WHERE c.FORENAME LIKE "%l%"

Here, I have not used any 2=0 for t1.skillid, simply because I think it
would be better to start the plan with t1, which can use the skillid
index, than to fool the optimizer to start with a (real or fake) NATURAL
on c (the ideal solution would be to avoid the 2=0 for the most unusual
skill).

Firebird 2.0 may suggest other plans for queries than Firebird 1.5 does,
in general I think it does a better job, but in some cases it is worse.
I don't think Arno has removed the possibility for hinting the optimizer
through adding 2=0 or +0 (I certainly wouldn't like that to happen)

Whether Firebird 2.0 helps in your particular situation, I do not know.
I think you have to try for yourself.

Set

Tom Conlon wrote:

> Hi Arno,
>
> Many thanks for the reply.
>
> What would be the preference in terms of the two metadata statements
> from my first post?
>
> Also BTW - below is an example of the actual SQL generated by the app:
>
> SELECT DISTINCT c.contid, c.surname, c.forename, c.dob, c.postcode,
> c.filed, c.available, c.email
> FROM cont c
> JOIN contskill t1 ON (c.contid=t1.contid) AND (t1.skillid=29 )
> JOIN contskill t2 ON (c.contid=t2.contid) AND (t2.skillid=34 )
> JOIN contskill t3 ON (c.contid=t3.contid) AND (t3.skillid=247 )
> JOIN contskill t4 ON (c.contid=t4.contid) AND (t4.skillid=256 )
> JOIN contskill t5 ON (c.contid=t5.contid) AND (t5.skillid=321 )
> JOIN contskill t6 ON (c.contid=t6.contid) AND (t6.skillid=3024 )
> JOIN contskill t7 ON (c.contid=t7.contid) AND
> (
> (t7.skillid+0 IN (118,136,784,1789,2184,2393) ) OR
> (t7.skillid=1402 AND t7.rating=1 )
> )
> WHERE ((c.contid>0) AND ( c.FORENAME LIKE "%l%" ) )
>
> Notice in the OR case (t7) I have to add t7.skillid+0 and also some
> situations need the (c.contid>0) to help it along. Sad to say but it's
> a bit frustrating to have to tweak in this way...Does FB2 fix this?
>
> Tom
>
> --- In firebird-support@yahoogroups.com, "Arno Brinkman"
> <fbsupport@...> wrote:
>> Hi,
>>
>> At least remove the distinct from the EXISTS predicate.
>>
>> AFAIU your query you could also merge the exists into the "base" from.
>> Or move "contskill t1" also to the EXISTS and the DISTINCT can be
> removed at both places.
>> SELECT DISTINCT
>> c.contid, c.surname, c.forename, c.dob,
>> c.postcode, c.filed, c.available, c.email
>> FROM
>> cont c
>> JOIN contskill t1 ON (t1.contid = c.contid AND t1.skillid = 10)
>> JOIN contskill t2 ON (t2.contid = c.contid AND t2.skillid = 17)
>> JOIN contskill t3 ON (t3.contid = c.contid AND t3.skillid = 18)
>> JOIN contskill t4 ON (t4.contid = c.contid AND t4.skillid = 22)
>> JOIN contskill t5 ON (t5.contid = c.contid AND t5.skillid = 24)
>> JOIN contskill t6 ON (t6.contid = c.contid AND t6.skillid = 29)
>> JOIN contskill t7 ON (t7.contid = c.contid AND t7.skillid = 30)
>> JOIN contskill t8 ON (t8.contid = c.contid AND t8.skillid = 34)
>> JOIN contskill t9 ON (t9.contid = c.contid AND t9.skillid = 4209)
>> JOIN contskill t10 ON (t10.contid = c.contid AND t10.skillid = 62)
>>
>>
>> SELECT
>> c.contid, c.surname, c.forename, c.dob,
>> c.postcode, c.filed, c.available, c.email
>> FROM
>> cont c
>> WHERE
>> EXISTS(
>> SELECT 1 FROM
>> contskill t1
>> JOIN contskill t2 ON (t2.contid = c.contid AND t2.skillid = 17)
>> JOIN contskill t3 ON (t3.contid = c.contid AND t3.skillid = 18)
>> JOIN contskill t4 ON (t4.contid = c.contid AND t4.skillid = 22)
>> JOIN contskill t5 ON (t5.contid = c.contid AND t5.skillid = 24)
>> JOIN contskill t6 ON (t6.contid = c.contid AND t6.skillid = 29)
>> JOIN contskill t7 ON (t7.contid = c.contid AND t7.skillid = 30)
>> JOIN contskill t8 ON (t8.contid = c.contid AND t8.skillid = 34)
>> JOIN contskill t9 ON (t9.contid = c.contid AND t9.skillid = 4209)
>> JOIN contskill t10 ON (t10.contid = c.contid AND t10.skillid =
> 62)
>> WHERE
>> t1.contid = c.contid AND t1.skillid = 10)
>>
>> Regards,
>> Arno Brinkman
>> ABVisie