>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 ?
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 ?