Subject Re: [firebird-support] Index: Newbie Questions
Author Ann W. Harrison
At 10:38 AM 11/19/2004, Zakir Mahomedy wrote:

>I understand the concept of using index, however there are some few things
>that I haven't yet grasp.
>1) Creating an index on 2 or more columns, helps the query in what way?
> eg) I have a table inventory where I can search by itemid or
> I can create an index for itemid and another index for description
>to speed up searching for each column.
> Now what would an index on both itemid and description serve?

If you already have a index on each term, the compound index
just gets in the way. If, however, you always ask for items
by warehouse and shelf, and never by shelf alone, a compound
index on warehouse and shelf is better than two separate indexes
on warehouse and shelf because it has fewer duplicate values.
If the fields are listed as warehouse, shelf, you can use that
index to lookup everything in a warehouse or everything on
a particular shelf in a warehouse.

>2) Making the index unique impacts the index in what way?

The index will reject duplicate values. It's the old fashioned
equivalent to a unique constraint.