Results 1 to 4 of 4

Thread: where statment

  1. #1
    Join Date
    Dec 2009
    Posts
    27

    Unanswered: where statment

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2009
    Posts
    27
    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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    the access path SHOULD be the same on both queries. Order of the predicates should not matter.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •