In fact, if my memory is correct, back when I took the tuning course, that was what they said. If you have a combined index of fields 1,2, and 3. And use any combination of those fields, it will use the combined index. If you have three indexes it can get screwy as to which one it will use first. If you are using a cost based indexing rule then it will figure out which one will give it the best bang for the buck. Personally, I prefer a little more control over it and try to build my indexes based on what gives me the best cutting down of the table data also. If you don't have enough unique data in your column than your index will not be useful. I can't remember exactly what that level was. But I want to say around 10%.
As such, indexes off of status can be useful unless they are looking for 'Status' = "Closed". IIRC, you would be better off using 'Status' > (whatever is before closed in your environment). But again, you have to play with variations of your query and do timings for each variation. It is loads of fun and I hope you love the reward of knocking off a small percentage of time for each calculation, which can run into a huge benefit if you are in the right piece of workflow. Brian On Wed, Apr 13, 2011 at 1:43 PM, LJ LongWing <lj.longw...@gmail.com> wrote: > Well....in my humble opinion, having all 3 of those indexes would be > wasteful and unnecessarily resource intensive because #1 contains > everything > that's in #2 AND #3, so forcing the DB to maintain all 3 would be > unnecessary overhead....but the answer to your question is 'I don't know, > check with your DBA' :)....as Brian mentioned, every DB is a little > different in how it does this sort of thing, and everything I've said is a > 'general rule', but not a hard fast law for every DB out there...I > primarily > work in SQL Server...it's my understanding that Sybase is similar in > function....Oracle not so much...so YMMV. In the situation below though, > if > you had a need for a 3 deep compound index like that, I wouldn't also have > #2 and #3, and I believe that all things relative, the query would use #1 > in > the absence of the other two. > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Pruitt, Christopher (Bank of > America Account) > Sent: Wednesday, April 13, 2011 12:33 PM > To: arslist@ARSLIST.ORG > Subject: Re: Question about Indexes and Order of Qualification Statements > in > Filters or Active Links > > One last question. > > If a form had several indexes on it. > > Index1: field1, field2, field3 > Index2: field1, field2 > Index3: field1 > > And the query was 'field1' = "1" which index would it use? > > Would it use Index1 and ignore the other indexes or would it skip to > Index3? > > > Christopher Pruitt > Business Consulting III > HP Enterprises Services > christopher.pru...@hp.com > www.hp.com > > > Confidentiality Notice: This message and any files transmitted with it are > intended for the sole use of the entity or individual to whom it is > addressed, and may contain information that is confidential, privileged, > and > exempt from disclosure under applicable law. If you are not the intended > addressee for this e-mail, you are hereby notified that any copying, > distribution, or dissemination of this e-mail is strictly prohibited. If > you > have received this e-mail in error, please immediately destroy, erase, or > discard this message. Please notify the sender immediately by return e-mail > if you have received this e-mail by mistake. > > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Pruitt, Christopher (Bank of > America Account) > Sent: Wednesday, April 13, 2011 1:12 PM > To: arslist@ARSLIST.ORG > Subject: Re: Question about Indexes and Order of Qualification Statements > in > Filters or Active Links > > I additional questions... > > If you have a single index using field1, field2, field3, would there be a > performance difference in the following qualifications? > > 1) 'field1' = "1" AND 'field2' = "2" AND 'field3' = "3" > 2) 'field3' = "3" AND 'field2' = "2" AND 'field1' = "1" > 3) 'field1' = "1" AND 'field2' = "2" AND 'field3' = "3" AND 'field4' = > "4" > 4) 'field4' = "4" AND 'field1' = "1" AND 'field2' = "2" AND 'field3' = > "3" > > Would there be a performance difference between the following indexes for > the query 'field1' = "1" > > Index: field1 > OR > Index: field1, field2, field3 > > > Christopher Pruitt > Business Consulting III > HP Enterprises Services > christopher.pru...@hp.com > www.hp.com > > > Confidentiality Notice: This message and any files transmitted with it are > intended for the sole use of the entity or individual to whom it is > addressed, and may contain information that is confidential, privileged, > and > exempt from disclosure under applicable law. If you are not the intended > addressee for this e-mail, you are hereby notified that any copying, > distribution, or dissemination of this e-mail is strictly prohibited. If > you > have received this e-mail in error, please immediately destroy, erase, or > discard this message. Please notify the sender immediately by return e-mail > if you have received this e-mail by mistake. > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of LJ LongWing > Sent: Wednesday, April 13, 2011 12:31 PM > To: arslist@ARSLIST.ORG > Subject: Re: Question about Indexes and Order of Qualification Statements > in > Filters or Active Links > > Christopher, > 1 - Run-If quals don't hit the DB (unless it contains a DB., but even then > it doesn't use your qual), so no the run-if order does not matter > 2 - The Set/Push if quals do however matter. Based on my research and what > I've been told by others significantly more qualified to answer this > question than myself, the order of the variables matters, and that in > general, a DB will only use a single index for a single query. So if you > have two fields, status and create date, with an index on both and a search > on both, the DB will figure out which one will give it the better results, > and use that one, but not both. If you want it to use both, it would need > to be a composite index so if your query was 'Status' = "Resolved" AND > 'Create Date' > $DATE$, your composite index would need to be status, then > create date, in that order...putting it in the other order would not have > it > use the index at all. And it is my understanding that the indexed fields > should be the first fields in the query statement to better enable the DB > to > utilize the indexes. > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Pruitt, Christopher (Bank of > America Account) > Sent: Wednesday, April 13, 2011 10:52 AM > To: arslist@ARSLIST.ORG > Subject: Question about Indexes and Order of Qualification Statements in > Filters or Active Links > > Hello Listers, > > We are looking at ways to make workflow (Filters/Active links) queries to > the database quicker. We have been working on optimizing our indexes but a > question came up that really stumped me. The question was "Does the order > in > a qualification matter?" I am hoping someone wiser than me on this list > could provide some insight. > > Say you have three fields, as a single index, and you are referencing those > three fields in a Run If, Set Fields If, or Push Fields If qualification. > > For this question let say you have 3 indexed fields in the qualification > along with 2 non-indexed fields. > > Should those indexed fields always start the qualification statements first > or does it matter where they are placed in the qualification? > > The question gets down to: If 2 non-indexed fields start the qualification > and the 3 indexed fields follow, will this cause a slower response from the > database, as opposed to using the 3 indexed fields first and then the 2 > non-indexed field last? > > > Christopher Pruitt > Business Consulting III > HP Enterprises Services > christopher.pru...@hp.com > www.hp.com<http://www.hp.com/> > > > Confidentiality Notice: This message and any files transmitted with it are > intended for the sole use of the entity or individual to whom it is > addressed, and may contain information that is confidential, privileged, > and > exempt from disclosure under applicable law. If you are not the intended > addressee for this e-mail, you are hereby notified that any copying, > distribution, or dissemination of this e-mail is strictly prohibited. If > you > have received this e-mail in error, please immediately destroy, erase, or > discard this message. Please notify the sender immediately by return e-mail > if you have received this e-mail by mistake. > > > > > > ____________________________________________________________________________ > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 > www.wwrug.com ARSList: "Where the Answers Are" > > > ____________________________________________________________________________ > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" > > > ____________________________________________________________________________ > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" > > > ____________________________________________________________________________ > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" > -- Brian Goralczyk Phone 574-643-1144 Email bgoralc...@gmail.com _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
