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 <mailto:mbritt...@navisite.com> 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"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
