"Arved Sandstrom" <asandstrom@[EMAIL PROTECTED]
> wrote in message
news:Wmq8k.167$1o6.154@[EMAIL PROTECTED]
> "Roy Hann" <specially@[EMAIL PROTECTED]
> wrote in message
> news:O5-dnXaHnYG1h__VnZ2dnUVZ8hednZ2d@[EMAIL PROTECTED]
> > "David Segall" <david@[EMAIL PROTECTED]
> wrote in message
> > news:nu2464djgj6vku4tci9if3ics221qidcp5@[EMAIL PROTECTED]
> >> "David Cressey" <cressey73@[EMAIL PROTECTED]
> wrote:
> >>
> >>> Because
> >>>there was an index with a compound index key, namely country, state,
and
> >>>city.
> >> Why would you do that instead of indexing the fields separately?
> >
> > Most DBMS engines will use only one index for a particular restriction
> > step, so in general you will want to define compound keys where they
make
> > sense.
> >
> > Depending on the implementation of the index it might be possible to
> > exploit right-incomplete key values, so you would try to define your
key
> > with the most frequently known parts of the key are on the left. Then
> > although the key may include more columns than you can supply a value
for,
> > you might still be able to exploit it if you supply the parts you do
know.
> >
> > Roy
>
> How common is the latter implementation? MySQL evidently does this, so
that
> if you had an index on (col1,col2,col3), queries on (col1), (col1,col2)
and
> (col1,col2,col3) all use the index, but a query on (col2,col3), for
example,
> does not. What is this incomplete use of multicolumn indexes called,
> exactly? I ran across articles that referred to this as partial
indexing,
> but my understanding of a partial (filtered) index is that the latter
> indexes on a subset of rows, which is something different. I'm guessing
that
> for any given DBMS you'd just have to look up the docs on multicolumn
> indexing and see what they say about using leading columns.
The term I've seen in the literature is "range retrieval". Range
retrieval
also describes
criteria like "where salary between 50000 and 100000".
>
> As I understand it, it wouldn't be totally useless to have only the
separate
> indexes for the three columns, namely (col1), (col2), and (col3), as the
> DBMS might use index combination (as does PostgreSQL 8.1 and up). Or
perhaps
> even better, have the multicolumn index and one or more single-column
> indexes, depending on what you anticipate the most common queries to be.
>
Yes but... the more indexes the slower inserts will run. There are
multiple
tradeoffs here.
|
