How to optimize search on multi cols?

[hmcheung]

How should I create index on the SQL server so that the following query is optimized?

select * from table1 where col1='aaa' and col2='bbb' and col3='ccc'

Thank you very much!

[simonm]

Indexing the three fields might be an option (three seperate indexes that is).

It's usually worth indexing a column if the column's values are not mostly the same thing or are not all totally different.

For example, you wouldn't bother indexing a BIT field as there are only two possible values (OK, three including NULL).

[brock landers]

Swings and roundabouts!

You must way up the following: -

1. How much data insertion is going to happen
2. How big the table is going to get
3. The other possible search criteria

I tend to max out on the indexing, but it can impare on data submission as indexes must be maintained when delete, update and insert are called.

The size of the table would impact on how many indexes you have. Some people create index tables that are larger that the table they are searching.

I never try and cover all possibilities. Cover the main variations (in you case create a three field index). You can always trace for slow queries and use that trace file to tweak even further.

Oh - tell the bloke who said that a bit has three values - no a bit has two - that is why it is called a bit !

[simonm]

OK, I know NULL isn't technically a value but it's still distinguishable from 1 or 0 and is therefore a third allowable state for a BIT field.

Only create a combined index on all three fields if you will always search on those fields together.

[brock landers]

Simon me old buddy

A pure bit field can only ever hold two values. If you allow bits to hold NULL's they are not longer bits! You could call them tri's (or twits), but not bits.

Come on, don't go bending 70 year old laws now.

[simonm]

OK, Maybe a "pure" bit field should not allow NULL values but surely you agree with the point I was making in that you wouldn't bother indexing a BIT field would you?

[jxiong]

you should create an index by combining the
three fields from the most unique (left) to the least
unique (right) as follows:

CREATE INDEX your_index_name on
tablename(col1,col2,col3);

Col1 should be the most unique (ie, 100% unique)
Col2 should be 80%
col3 should be >= 60%. If it is <= 50%, skip it

[simonm]

jxiong,

What's the point of indexing a field that is 100% unique?

Surely the index would take as long to search as the table itself?

[jxiong]

Well, unique and sorted are not the same!

Let say you have 26 unique chars, ie, A-Z, would you
agree that it would take you longer to find the
char W if it is not sorted?

Your logical is valid for unique, but does not make it
faster as far as searching for the specific key/value.