Re: Indexes

[<ma...@li...>]

Dieter,

Inexact searching aside, primary key and foreign key indexes, along with
candidate key indexes, should be provided at a minimum for each relation
in the system.

As well, you will find that RDBMS systems nowadays are substantially more
intelligent about using partial keys and opportunistic use of available
index to accelerate ad hoc queries.  If you provide no indexing, then the
RDBMS cannot use this intelligence.

There is no sound argument against doing this in the general case.  (For
example, if most users have relatively small installations, they will not
pay a substantial reindexing penalty in any case.)  Within limits, I think
benchmarking will show that a range of indexes should be provided.  True,
some queries can't or won't take advantage of indexing.  Over time, you
simply minimize your reliance on those sorts of queries.

Qaexl has provided a large number of indexes, more by far than I would
have assumed most users would need (or want).  However, he based his list
on benchmarking results, so maybe he as found some useful speedups worth
looking into, yes?

Matt


On Mon, 7 Jan 2002, Dieter Simader wrote:

> Indices are only useful for an exact search not a fuzzy search as is used
> on most of the queries looking for a name or partnumber.
>
> example:
> dws=# create index parts_idx on parts (partnumber);
> CREATE
> dws=# explain select * from parts where partnumber = '123';
> NOTICE:  QUERY PLAN:
>
>

-- 

Matt Benjamin

The Linux Box
206 South Fifth Ave. Suite 150
Ann Arbor, MI  48104

tel. 734-761-4689
fax. 734-769-8938
cel. 734-216-5309
pgr. 734-431-0118