"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.
>
In general, true. However, the optimizer in the DBMS in this case would
have been capable of exploiting separate simple indexes. The question
remians, why would the index designer have chosen a single index with a
compound key over multiple single indexes.
Several possible reasons:
Query speed. For the query that includes country, state, and city, the
single index can locate the right rows with fewer logical disk reads, and
therefore fewer physical disk reads.
Update speed. It takes fewer disk writes to commit a new row with one
compound index than with multiple simple indexes.
Psychological factors. Just as designers tend to think of a schema with
fewer tables as "simpler" than than one with more tables, so likewise
they
might think of a table with fewer indexes as "simpler".
Why didn't I change the index design? I was a visiting instructor for one
week. I didn't know what the other consequences of changing the index
design would be. The original designer may have had considerations that
eluded me. Index design involves anticipating traffic. There's always a
little guesswork in it.
Besides, I was teaching programmers tuning, and this gave me an
excellent
op****tunity to teach a key point: that a query that provides more
selection
criteria can run a lot faster than one with fewer criteria. This is
counter
intuitive to programmers. And a real live case from their production
database was worth more than a score of prepared cases from my course
materials.
> 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.
>
This is absolutely correct. But, for reasons that I can't really fathom,
people tend to design
compound indexes in top down order: country, state, city. I imagine that
city, state, country would almost always be more useful. But both
programmers and designers have been trained not to ever think bottom up,
so
they reject a bottom up index without careful analysis. This is just my
take on it.
However, the top down index might be more useful in a large re****ting
query,
where the re****t is being grouped by country, state, city. There are a
lot
of things to consider when doing index design.
|
