Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: Indexing Question

    Hi Gurus,
    I have a table called Companies with int identity column as primary key and other fields. Also there is a Status column which can hold either 0 or 1. I use this status column in a join from some child table like where a.status = 1 along with other conditions.

    Now, the question is should I create an index for this Status column? Will it improve the performance?

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    I would say NO. One of the criteria for creating a good index is selectivity. So your index on a booleon column would not help the performance. In addition, it is just an overhead on the inserts.

    - CB

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Post the query...because the answr is it depends...

    If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
    INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2

    I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    I agree with Brett. In the situation that he described (covered indexes), it could be helpful to tag that column at the end of the composite index to avoid another trip to get the data.

    - CB
    Originally posted by Brett Kaiser
    Post the query...because the answr is it depends...

    If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
    INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2

    I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    Ok, here is a sample:

    SELECT A.*, B.NAME
    FROM Orders A,
    Companies B
    Where B.CompanyId = A.CompanyId
    and B.Status = 1
    ORDER BY B.NAME

    Hope this helps.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    In this situation, adding status to the index will not help, unless Brett thinks otherwise.

    - CB
    Originally posted by shekarnarayanan
    Ok, here is a sample:

    SELECT A.*, B.NAME
    FROM Orders A,
    Companies B
    Where B.CompanyId = A.CompanyId
    and B.Status = 1
    ORDER BY B.NAME

    Hope this helps.

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Quick question,.. why don't you try it and see what happens? Worse comes to worst you can just delete it afterwards....

  8. #8
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    Agreed, just try it. Set up a test/dev environment. Run query before index added, look at query execution plan, apply index and look once again at query execution plan. It will help.

  9. #9
    Join Date
    Nov 2003
    Posts
    4
    Well, I tried as suggested and the execution plan does not seem to use the new index at all! It just uses the clustered PK index. So I guess the answer is NO to the new index.

    Thanks for all the suggestions.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT *....

    No, No, No....

    Do you really need all of the columns?

    If so, list them out...

    Only use SELECT * for testing, analysis...

    What's the DDL for the 2 tables?

    And the optimizer is making the right call in your case

    How many rows of data are we talking about?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You say your column only holds ones and zeros. If it is a bit field it cannot be indexed. Even if it is not a bit field, if the distribution of values for one and zero are about 50%, the optimizer might not get much out of using the index. In a binary tree it would only save 1 search ply.

    blindman

  12. #12
    Join Date
    Nov 2003
    Posts
    4
    Originally posted by Brett Kaiser
    SELECT *....

    No, No, No....

    Do you really need all of the columns?

    If so, list them out...

    Only use SELECT * for testing, analysis...

    What's the DDL for the 2 tables?

    And the optimizer is making the right call in your case

    How many rows of data are we talking about?
    Hi Brett,
    Thank you for your concern. Yes I do list all the fields and never use the * from my programs. Number of records in the comp. table is around 500 and the orders table may be few thousands. I also filter by company.

  13. #13
    Join Date
    Feb 2002
    Posts
    2,232
    On such a small number of records, you will not see much of an improvement. Anytime you have so a limited distribution like yes/no, male/female ... the optimizer will normally chose a table scan over an index (so normally the recommendation is No Way). Unless your distribution is very high for 1 value and very low for the other value, an index will only help for the low value anyway. If the distribution of these values are remotely close to each the optimizer will probably perform a table scan anyway. Since these tables are small, sql will probably chose a table scan over an index even if your distribution is ripe for an index.

Posting Permissions

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