Hi LJ, Is the term you were searching for "index selectivity"?
I would think that in this case an index on assignee name would be the most effective as it would be pretty selective always. I guess when the database grows large which is when you need your index then an index on "status" would be selective enough to make a difference. This index will immediately ignore all of the old closed tickets and just find the hopefully few open ones. If it were me I'd index A and C seperately and forget about B. I would expect the A index to be ignored until the database got very large and the query was against open tickets. Rod On 17/09/2009, LJ Longwing <lj.longw...@gmail.com> wrote: > ** > I agree with reversing ABC to CBA, but only if C is ....oh damn I always > forget the DB term...is it 'referencable'?....the term I'm looking for means > if you have a data set of 1000 records and 900 of them have value X, and > that field is indexed...searching on X is likely not to use the index > because it's not unique enough to make it worth it for the DB. In your case > Assigned Person may be unique enough to reverse the compound index. > ________________________________ > From: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org] On Behalf Of ARSmarts Support > Sent: Thursday, September 17, 2009 9:41 AM > To: arslist@ARSLIST.ORG > Subject: Re: Indexing help > > ** Hi Mark, > > You are right. If you create an index on ABC, and search on BC, the query > will not use an index. > If you search on A, AB or ABC, you query may use the index. > > The 3 indexes you create are not redundant, but I would not do so. I would > reverse the order of your first index (Assigned Person, Assigned Group, > Status). This way you do not need to create the index 3. > > You must always remember that you can search on fields in the order they are > defined in the index. > > Jean-Louis Halleux > www.arsmarts.com > > > On 17 Sep 2009, at 17:30, Brittain, Mark wrote: > > ** > Good morning, > > I think I inherited a mess and need some help with indexing. If I have an > index > Status > Assigned Group > Assigned Person > > And the query is Assigned Group and Assigned Person does the index get > ignored because Status was not used? > > What if I have similar indexes like these > > Index 1 > Status > Assigned Group > Assigned Person > > Index 2 > Assigned Group > > Index 3 > Assigned Person > > Are these redundant? > > Also is there and good information out there that really gets into the nuts > and bolts of indexing? > > Thanks > Mark > ____________________________________________ > Mark Brittain > Remedy Developer > NaviSite > mbritt...@navisite.com > (315) 453-2912 x5418 (Phone) > (315) 317.2897 (Cell) > Reduce Cost of IT with Managed Hosting and Application Services from > NaviSite. > Visit www.NaviSite.com Today. > > > ________________________________ > This e-mail is the property of NaviSite, Inc. It is intended only for the > person or entity to which it is addressed and may contain information that > is privileged, confidential, or otherwise protected from disclosure. > Distribution or copying of this e-mail, or the information contained herein, > to anyone other than the intended recipient is prohibited. > _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where > the Answers Are"_ > _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers > Are"_ > _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers > Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
