Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: indexes - doubts

    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.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    Thanks Mike,

    another questions in topic subject:

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

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    yes, no problem there.

Posting Permissions

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