Hi experts,

I have a table with primary key formed by two fields:
VENDOR & MANUFACTURER_PART_NUMBER

This table has more than 2 million records.

If i search a particular MANUFACTURER_PART_NUMBER it takes about 5-6 seconds.

My question is: in my situation , if MANUFACTURER_PART_NUMBER field is part of primary key, it's necessary or it's OK to create index on this field?

if MANUFACTURER_PART_NUMBER field is part of primary key, it's indexed,right? ... so, why a simple query takes so long?

Thanks in advance.

Imagine looking up a word in the dictionary, if you know the first few letters then it's easy and quick. Now imagine looking up a word where the only letter you don't know is the the first letter - it's impossible as you'll have to look through all the words in the dictionary.

It's the same thing when looking for a record in a table using a composite index but not knowing the first field in the composite index - you can only table scan the all the records.

I'm not sure what you're current index is but either MANUFACTURER_PART_NUMBER should be the first field in the composite index or there should be another index on the table just on this field.

Thanks Mike,

another questions in topic subject:

it's OK to create index on fields which types are date?

yes, no problem there.