Michel,

>Here is a SQL that I am using as is. I just changed the table names:
>
>

>SELECT Member.*,Item.AI FROM Member;
> INNER JOIN Status ON Member.NoStatus=Status.Numero;
> INNER JOIN Item ON Member.NoDBUsers=Item.Numero;
> WHERE Item.Numero=3042 AND Member.NoEntity=1 AND;
> (Status.Numero=2 OR Status.Numero=9 OR Status.Numero=3 OR Status.Numero=5 OR Status.Numero=16)
>

why did you include the Status table in the first place ?
Since the Join hinges on Member.NoStatus=Status.Numero, my guess would have been>

SELECT Member.*,Item.AI FROM Member;
 INNER JOIN Item ON Member.NoDBUsers=Item.Numero;
 WHERE Item.Numero=3042 AND Member.NoEntity=1 AND;
 (Member.NoStatus=2 OR Member.NoStatus=9 OR Member.NoStatus=3 OR Member.NoStatus=5 OR Member.NoStatus=16)

and some shorter forms should also be optimized the same way if Member.NoStatus is indexed, like

SELECT Member.*,Item.AI FROM Member;
 INNER JOIN Item ON Member.NoDBUsers=Item.Numero;
 WHERE Item.Numero=3042 AND Member.NoEntity=1 AND;
 Member.NoStatus IN (2, 9, 3, 5, 16)

or

SELECT Member.*,Item.AI FROM Member;
 INNER JOIN Item ON Member.NoDBUsers=Item.Numero;
 WHERE Item.Numero=3042 AND Member.NoEntity=1 AND;
 INList (Member.NoStatus, 2, 9, 3, 5, 16)

>But, here is the interesting part. If I add another set of fields after Item.AI, the result is fully optimized:
>

>SELECT Member.*,Item.AI,Status.* FROM Member;
> INNER JOIN Status ON Member.NoStatus=Status.Numero;
> INNER JOIN Item ON Member.NoDBUsers=Item.Numero;
> WHERE Item.Numero=3042 AND Member.NoEntity=1 AND;
> (Status.Numero=2 OR Status.Numero=9 OR Status.Numero=3 OR Status.Numero=5 OR Status.Numero=16)
>

Is the join order reported by sys(3054) the same for both of your SQL variants ?

>So, this shows that there is something buggy in the way the engine works.

Probably not. A few days ago was a similar thread where A. Tsingauz described why some expressions in SQL are optimized while others are not.

>But, it just doesn't make any sense that I will get an immediate result just because I add Status.* at the end of the field list.

I had asked for better description on when Rushmore is used and when other ways of query formulation will be faster - perhaps you can add to that.

On the old "approaching 2 Gig" thread - any problem ?

regards

thomas