Hello Gurus
My requirement is

to quickly select records based on either the ID column or the LAST_NAME or both columns together from my table say 'T'

For this
I want to create index on these two columns

Should I be creating


1 index specifying both columns, or

2 indexes (one on each), or

3 indexes (one for ID + one for last name + both)

Can you please advice ??

Thanks in advance

It depends. Is ID unique? Will last_name and ID always match? (whenever ID = X the last_name will always be Y)

Andy

Yes ID will be unique

But I would also be interested to know how the answer depends if

For One ID we have One Last Name


from


For one ID we have two to three last names ( max 5 )


Please advice

For your requirements, you need 2 indexes. One on ID and one on Last_Name.

If an ID had more than one last_name (It is no longer unique) and a last_name could have more than one ID, you would still need only 2 indexes and one would still be on ID only, but the second one would be on Last_name,ID.

Andy

Thanks Andy

I have around 4 million records in a table with around 25 rows

and yes I have multiple last names for an ID .
I think you recommended that

"If an ID had more than one last_name (It is no longer unique) and a last_name could have more than one ID, you would still need only 2 indexes and one would still be on ID only, but the second one would be on Last_name,ID."


In this I am thinking the sequence for second index matters and you mentioned it has to be last_name and then ID .

Is that right ?

Secondly , i also wanted to educate myself by knowing

What would be the over head of having a third index on only ID ?

Also does the create index sequence matters
Which means
1 Create index on ID , LAST NAME
2 Create index on last name
3 create index on id

will this be different than
1 create index on id
2 Create index on last name
3 Create index on ID , LAST NAME

I don't understand your table design (especially the part about one ID having multiple last names), but in DB2 you don't need Index 3 since it is redundant (DB2 can use Index 1 even if only ID is known).

Note that if only LAST NAME is known, Index 2 is still needed and is not redundant, since your predicate needs to match the first column of the index.

The order that indexes are created in do not matter. Having one index that is a subset of another is a redundant index and should be eliminated as it is not necessary. Having an index on ID and having an index on ID,Last_Name, the first index is redundant. What the indexes need to have depends on how you will access the data. If you will only use equivalent checks then you only need these indexes-- 1) ID, 2) Last_name, ID. If you use range checks then it depends on what columns are ranged.



Andy