Index Question

Database
Enthusiast

Index Question

creating a table with item, origin, destination.

what is the best way to index the table when the user may

query by either item/origin or item/destination?

Tags (1)
4 REPLIES

Re: Index Question

Do you mean 'Selecting the Primay Index' column?

There's a lot we would need to know to help you and to be accurate, but I would make your PI on Item, knowing almost nothing about your data.  Give it a try, it can't hurt!

Enthusiast

Re: Index Question

You can have only one primary index  per table.However, you can create secondary indexes.

So you can design in such a way that they are used in where clause.

Cheers,

Enthusiast

Re: Index Question

Tclear,

You can create PI on Item, and secondary indexes on Item/origin, item/destination to have optimal access.

Khurram
Enthusiast

Re: Index Question

Create the Primary Index with a colum of more unique values.

While creating Secondary index you need to see -

1) how frequently the combination of columns are being used in retrival. if usage is more then only prefer SI.

2) if any utilities such as Fastload, multiload are being used then rethink of creating SI

Sri