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