Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54

    Unanswered: Create index on joining tables

    I need to select rows from 2 tables using SQL:

    SELECT A.column1, A.column2, B.column1, B.column2 FROM table1 A join table2 B on A.column1 = B.column1 ORDER BY A.column1, B.column1

    How should I create an index to make the selection faster?
    Maybe I should create more than 1 indexes?

    Thanks!

    Bruce

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    For that query, there should be an index on a.column1 and another on b.column1. You "order by" is redundant, remove the b.column1 since it is the same as a.column1.

    Andy

  3. #3
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54
    Thanks Andy.

    Good point. Now suppose I change the ORDER BY to ORDER BY A.column1, B.column2

    SELECT A.column1, A.column2, B.column1, B.column2 FROM table1 A join table2 B on A.column1 = B.column1 ORDER BY A.column1, B.column2

    Then should I create 3 indexes?

    1. on A.column1
    2. on B.column1
    3. on B.column2

    Or should I create some sort of composite index to handle
    "ORDER BY A.column1, B.column2"

    Thanks.
    Bruce

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, you still only need 2 indexes. Just make the one on B column1,column2.

    Andy

  5. #5
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54
    Cool, Thanks Andy.

    What would be the general rules of making indexes for query like this: (A, B, C are 3 tables)

    SELECT ... FROM ... ORDER BY A.c1, B.c1, C.c1, A.c2, B.c2, C.c2

    Thanks
    Bruce

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Adding indexes just to satisfy an order by clause may not be the best idea. You get the most bang for the buck with indexes when they cover foreign key relationships and the predicates in a where clause.

    Andy

Tags for this Thread

Posting Permissions

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