"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.
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
|
