#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    Question 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)

    or

    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?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by KBek
    What operation is faster in real world?
    What happened when you tried?
    How do the execution plans look like?

    Comments on this post

    • mariuz agrees
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    14
    Originally Posted by alexisdiel
    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.

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

IMN logo majestic logo threadwatch logo seochat tools logo