>Hello,
>
>this is my query :
>
>Select
> MAINTABLE.ID
>from
> MAINTABLE
>Join SECONDTABLE on SECONDTABLE.ID_obj=MAINTABLE.ID_obj
>Join THIRDTABLE on THIRDTABLE.ID_obj=MAINTABLE.ID_obj
>where
> (MAINTABLE.name = 'jean' or
> SECONDTABLE.name = 'jean' or
> THIRDTABLE.name = 'jean')
>
>I have an index on
>
>MAINTABLE.name
>SECONDTABLE.name
>THIRDTABLE.name
>
>the probleme is that firebird use
>
>PLAN JOIN (THIRDTABLE NATURAL, SECONDTABLE INDEX (RDB$PRIMARY436), MAINTABLE INDEX (RDB$PRIMARY26))
>
>and it not use the index of the field
>MAINTABLE.name
>SECONDTABLE.name
>THIRDTABLE.name

Sorry, that's not possible. Logically, it has to start with one of the tables with no knowledge about the others and applying that index would only find, say, 1/3 of the cases. Moreover, unless ID_OBJ is unique, your query might not return what you want in all cases. If SecondTable contained two 'Jean's and ThirdTable three 'Jean's for the same ID_obj, that will give you at least six rows with the same MAINTABLE.ID. What you probably want is a simple UNION rather than OR:

SELECT ID
FROM MAINTABLE
WHERE name = 'jean'
UNION
SELECT MT.ID
FROM MAINTABLE MT
JOIN SECONDTABLE ST ON MT.ID_obj=ST.ID_obj
WHERE ST.name = 'jean'
SELECT MT.ID
FROM MAINTABLE MT
JOIN THIRDTABLE TT on MT.ID_obj=TT.ID_obj
WHERE name = 'jean'

(add ALL to each UNION if you want to include duplicates)

If things are more complex, you might consider using one or more CTEs (though it isn't really useful in this simple example):

WITH MyCTE(ID_OBJ) AS
(SELECT ID_OBJ
FROM MAINTABLE
WHERE name = 'jean'
UNION
SELECT ID_OBJ
FROM SECONDTABLE
WHERE name = 'jean'
SELECT ID_OBJ
FROM THIRDTABLE
WHERE name = 'jean')

SELECT M.ID
FROM MAINTABLE M
JOIN MyCTE CTE ON M.ID_OBJ = CTE.ID_OBJ

HTH,
Set