I have seen this quite a whlie and was wondering what can be done about it.
For example, I have a select which includes four INNER JOIN into the same table. The execution plan, instead of using the join table primary key index is using a boolean index instead. This makes it, of course, less optimizable. As the INNER JOIN lines are all bound to the primary key of the joined table, why doesn't it use it?
Here is an example:
LEFT JOIN Member ON Client.NoMember=Member.Numero
INNER JOIN Member AS Member2 ON Client.NoMember2=Member2.Numero
INNER JOIN Member AS Member3 ON Client.AddUser=Member3.Numero
INNER JOIN Member AS Member4 ON Client.ModUser=Member4.Numero
In that case, the index used for the Member table is a boolean field and I had expected it would use Member.Numero.