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.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|||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..|||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..|||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.|||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.|||Quick question,.. why don't you try it and see what happens? Worse comes to worst you can just delete it afterwards...|||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.|||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.|||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?|||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|||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.|||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.