Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: Index Performance?

    I have to large tables, A and B with the common columns c and d
    I make a select statement

    select * from A,B
    where A.c=B.c and A.d=B.d;

    Should my indexes on the two columns be common indexes or single indexes?

    create index INDX1 on A(c);
    create index INDX2 on A(d);
    create index INDX3 on B(c);
    create index INDX4 on B(d);

    or

    create index INDX5 on A(c,d);
    create index INDX6 on B(c,d);

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    For the given example, use the second alternative and put that column to the front which gives a higher selectivity (i.e. which returns less rows when queried with a specific value)

  3. #3
    Join Date
    Jun 2003
    Posts
    81

    To front?

    I am not sure, that I understand. What do you mean when you say, that I should bring til columns to the front?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by kfc@vd.dk
    I am not sure, that I understand. What do you mean when you say, that I should bring til columns to the front?
    There is a difference between

    create index INDX5 on A(c,d);

    and

    create index INDX5 on A(d,c);

    the first column in the index definition should be that column, which reduces the number of rows more than the other.

    If a select * from a where c = 5 returns 100 rows and a select * from a where d = 14 returns 50000 rows, then c should be the first column.

Posting Permissions

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