January 15th, 2013, 01:44 AM
What query is faster? JOIN, IN or EXISTS?
I have two tables:
A(id,a2) и B(a_id,b2)//A.id - PK, B.a_id - FK
N - amount of records in А , M - amount of records in B
What query is faster in FireBird 2.5
select B.b2 from B join A on A.id=B.a.id
select B.b2 from B where B.a_id in (select id from A)
select B.b2 from B where exists(select 1 from A where B.a_id=id)
Form math point of view
3-d faster. Time is M*(logN),
After that 2-d. Time is N+M*logN
And then 1-st. Time is M*N
But what about optimization of this operations in FireBird?
What operation is faster in real world?
January 15th, 2013, 02:17 AM
What happened when you tried?
Originally Posted by KBek
How do the execution plans look like?
Comments on this post
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions:
January 25th, 2013, 12:46 PM
Usually, I go with JOIN,
But the others can be fast too if you have created an index on B.a_id
So, in the real world... always depends of a few variables, like created indexes.
February 13th, 2013, 02:36 AM
Originally Posted by alexisdiel
Just like you said.
Join is good for usual cases because usually you'll need fields from both tables.
At bigger tables you'll see which method is better for you...