RE: Redundant indexes

From: Michael Lonski

Date: 6 May 2008

If the first index provides uniqueness, then the second index is
completely worthless as the third field doesn't add anything, not even
sorting.  

As the other have said about in your second example, it makes no sense
to pull linenum off to its own index.

If USE-INDEX has been used, that's a different matter.  Hopefully
there's some explanation in the code of why they felt the need for
forcing the index choice

Sincerely,
Mike
----------
Michael J. Lonski
V.P. Research and Development
(804) 553-1130, Ext. 206
www.AllegroConsultants.com
We Keep I.T. In Tune
* Order "Coding Smart" 2nd Edition - ADM2 "How To" book * 
> -----Original Message-----
> From:  [mailto:] On Behalf Of
> George Potemkin
> Sent: Tuesday, May 06, 2008 9:21 AM
> To: 
> Subject: Re: Redundant indexes
> 
> > If an index consists of fields, all of which are leading components
> of
> > another index, is it "better" to:
> >
> > 1) Delete the redundant index.
> >
> > 2) Remove the redundant fields from the other index (which really
> means
> > removing the index and creating another one without the leading
> components.
> 
> My choice:
> 3) Check _IndexStat
> 
> It's possible that the index (idx2) with redundant fields is not used
> at
> all. Then it can be deleted istead of the redundant index (idx1).
> 
> It's possible that update rate for redundant fields much higher than
> for the
> leading components. Then two indices can co-exist to solve the
resource
> contention.
> 
> The redundant index can be an unique one but nevertheless there might
> exist
> the reasons to create the indices with with redundant fields. In this
> case
> the first index should exist to provide the uniqueness for leading
> components.
> 
> But that are the exceptions. In most cases the redundant index must
be,
> IMHO, deleted.
> 
> > Once you've decided what to do from a schema perspective what would
> you
> > do if you discover that "the programmers" have been coding with
> > USE-INDEX instead of BY?
> 
> Do we have an option to kill the programmers? ;-)
> 
> Regards,
> George Potemkin
> Progress Technologies
> S.-Petersburg, Russia
> Phone: +7 (812) 438 2757
> http://www.progress-tech.ru