>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
