Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009

    Unanswered: Hitting an index

    Say I have a table, table1 with and index on field1,2,3

    If I query this table with a query similar to this:
    Select field4
    from table1
    where field1=xxx
    and field3=aaa
    and field2 =ccc
    I have listed all fields in the index in my where clause, but I didn't hit them in the order they're listed in the index. Will my index be used? Will is increase the speed of my query if I hit the fields of the index in order?

    And I'd like to know if I join this table to another using the index, would I have to join the table listing the fields in the index in order also.

    Like this:
    Select otherTable.*, table1.field4
    from otherTable
    join table1 on (othertable.x = table1.field1 and othertable.y=table1.field2 and othertable.y = table1.field3)
    ... or will just listing the fields in the index in any order work ( as long as I list the first field in the index first?)

    Thanks for your input ...

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you can mention the columns in any order you wish

    the optimizer is a lot smarter than you give it credit for | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    One Flump in One Place
    Regarding the second question, the order will not matter however based on omissions from the code fragment, it is possible that this index you specify would not be used anyway.

    Also, I think this is always best handled empirically yourself - why not try the different queries and look at the plans and execution plans?
    pootle flump
    ur codings are working excelent.

Posting Permissions

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