On Jun 25, 6:55 am, "Arved Sandstrom" <asandst...@[EMAIL PROTECTED]
>
wrote:
> "Roy Hann" <specia...@[EMAIL PROTECTED]
> wrote in message
>
> news:O5-dnXaHnYG1h__VnZ2dnUVZ8hednZ2d@[EMAIL PROTECTED]
>
>
>
> > "David Segall" <da...@[EMAIL PROTECTED]
> wrote in message
> >news:nu2464djgj6vku4tci9if3ics221qidcp5@[EMAIL PROTECTED]
> >> "David Cressey" <cresse...@[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?
Oracle calls it a range scan in the EXPLAIN PLAN re****t. And that
makes sense if you think about it. (at least it does to me.)
> ... 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.
>
> 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.
>
> AHS
If you must have one index, put the lower unit element first. So on a
phone number table
you might put Number, Exchange, Area code (e.g. 800-123-4567 indexed
as 4567,123,800). then partial searches on Exchange and number can use
the index.
Ed
|
