Yes....selectivity....:) -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Rod Harris Sent: Thursday, September 17, 2009 10:56 PM To: [email protected] Subject: Re: Indexing help
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 <[email protected]> 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:[email protected]] On Behalf Of ARSmarts Support > Sent: Thursday, September 17, 2009 9:41 AM > To: [email protected] > 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 > [email protected] > (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: [email protected] ARSlist: "Where the > Answers Are"_ _Platinum Sponsor: [email protected] ARSlist: > "Where the Answers Are"_ _Platinum Sponsor: [email protected] > ARSlist: "Where the Answers Are"_ ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
