Hello All,

Question: notice the change on the where statement, will the change impact performace if id field on table_a is indexed ? where id is unique value on table_a

select *
from table_a a,
table_b b,
table_c c
where a.id = b.id
and b.another_id = c.another_id
and a.id = 100;

OR

select *
from table_a a,
table_b b,
table_c c
where a.id = 100
and a.id = b.id
and b.another_id = c.another_id

I dunno why don't.....

you set up some tables,
populate them with some data
and run some tests for yourself?

who knows how the query optimiser will translate/parse your SQL. My expectation is that it will near identical,if not identical. but once you have built your tables, generated your test data and run your tests, you could follow up with an EXPLAIN to try to understand what is happening.

using an index on a column should, theoretically, make a query more efficient

i wonder if the optimizer will first look on the a.id (which is unique index) and loop only once checking a.id = b.id and b.o_id = c.o_id)
OR
it will loop all posible a.id = b.id and b.o_id = c.o_id and then test it against a.id = 100;

performace is a lot diffrent (multiple cartezian tables tests against 1 value) or (1 value test against multiple cartezia although smaller)

thanks
Chanan

the access path SHOULD be the same on both queries. Order of the predicates should not matter.
Dave