Arved Sandstrom, 25.06.2008 13:55:
> 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 think that this is related to the fact that most of the time the index
is implemented as a B-Tree index. And technically it is not possible to
access the "second level" directly without finding the "first level".
> 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 and no. Keep in mind that each index will add an overhead when
updating, deleting or inserting rows. So if you have a lot of indexes,
your update performance may degrade. The "art" is finding the proper
balance between the two requirements (fast reads and fast writes)
Postgres does a indeed good job in "re-using" indexes. For e.g. Oracle
something similar could be achieved using bitmap indexes (but they have
higher update costs and are not always feasible)
Thomas
|
