Results 1 to 10 of 10

Thread: index

  1. #1
    Join Date
    May 2008
    Posts
    24

    Smile Unanswered: index

    hi,
    I have a sql on a table like below

    select field1, field2 from table1
    where field3 = ?
    and filed4 = ?

    I have just created a composite index on the filed3&field4

    Will that do? or should i add the fields in select clause also to this?

    Thanks

  2. #2
    Join Date
    Jun 2009
    Location
    Lisboa, Portugal
    Posts
    78
    I don´t understand the question. You don't need any index to do that query, you can create an index to make it quiker or for other proposes. What is your problem?
    LS

  3. #3
    Join Date
    May 2008
    Posts
    24

    Exclamation

    Quote Originally Posted by Luis Santos View Post
    I don´t understand the question. You don't need any index to do that query, you can create an index to make it quiker or for other proposes. What is your problem?
    My question is, how to pick the columns for the index and on which order?

    for instance, in the below query-
    select
    from a, b, c
    where a.a1 = b.b1
    and b.b1 = c.c1
    and c.c2 = "AB"

    so, in this case, which is the efficient way of indexing, shoul i go for composite indices?

  4. #4
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    The first column in a composite index should be the one you use most frequently.

    For example you often join the table with c1 but you dont have c2 in the where condition all the time:
    CREATE INDEX idx_c_c1_c2 ON c (c1, c2);

    But I would prefer two Indexes:
    CREATE INDEX idx_c_c1 ON c (c1);
    CREATE INDEX idx_c_c2 ON c (c2);

  5. #5
    Join Date
    May 2008
    Posts
    24
    Quote Originally Posted by InformixWilli View Post
    The first column in a composite index should be the one you use most frequently.

    For example you often join the table with c1 but you dont have c2 in the where condition all the time:
    CREATE INDEX idx_c_c1_c2 ON c (c1, c2);

    But I would prefer two Indexes:
    CREATE INDEX idx_c_c1 ON c (c1);
    CREATE INDEX idx_c_c2 ON c (c2);
    Thanks for the info!
    I just have another doubt on ordering the columns for my index.

    For example, i have a join as below
    WHERE c1.c = c2.c
    AND c1.d = ?

    In table c1, field c has the most distinct count when compared to field d.
    so the index should be like idx_c1(c,d).

    But since my query gets the field 'd' value as i/p, will changing the order as (d,c) helps me.

    Thanks

  6. #6
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    Is your table so big that this is important?

    Check the time your query runs without an index, create one, check again, create another one and check again.

  7. #7
    Join Date
    May 2008
    Posts
    24
    Quote Originally Posted by InformixWilli View Post
    Is your table so big that this is important?

    Check the time your query runs without an index, create one, check again, create another one and check again.
    Thats a good apprach!! My table is huge than you think, it has millions, thats why i'm concentrating more on this.

    Have you tried SET EXPLAIN ON in isql, I tried that but i couldn't find the out file, i guess it should go to the current directory, but it doesn't.

    Could you please help me on this?

    Many Thanks!!!

  8. #8
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    Try "set explain file to '/tmp/explain';"

    Are there a lot of different querys on this table or only one or two?

  9. #9
    Join Date
    May 2008
    Posts
    24
    Quote Originally Posted by InformixWilli View Post
    Try "set explain file to '/tmp/explain';"

    Are there a lot of different querys on this table or only one or two?
    "set explain file to '/tmp/explain';" fails!!!
    I'm getting syntax error.

    I'm using isql version -7.30.HC6

    Are there a lot of different querys on this table or only one or two?

    YES, there are different query's with differenct WHERE criteria's.

    Cheers!

  10. #10
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    Okay, I use DBAccess with IDS 11.50.UC6DE.

    Take a look in the INFORMIXDIR, I remind that there was something but its also possible that I was there when I started dbaccess.

    When there are a lot of different kind of querys, I would not create special composite indices but indices on single columns.

    If a lot of querys use the same columns in the where condition then you can create a composite on it.

    With IDS 11.50 a composite index can be used better.

Posting Permissions

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