If your index is field1, field2, field3
then 1 - would use index 2 - would NOT use index 3 - would use index 4 - I believe it would use index If your index is field1 then I think it would use the index in all 4 situations, but the index would be less likely to be used depending on referencability. In ALL cases above when it say it WILL use the index, that means of course that it would have the possibility, but not necessarily do so. Referencability of an index means that it determines how likely a query is to find useful data in the index. Let's use status as an example. If you have a ticket table with 1,000,000 rows in it and an index on Status. Let's assume your statuses are New, In Progress, and Complete (0,1,2 respectively). Let's say 950,000 rows are 'Complete', 25,000 are in 'In Progress' and 25,000 are in 'New'. So....if you have a search that says 'Status' < "Complete", then the DB can quickly ascertain that it will be returning roughly 50K of the total 1M, so it's likely that it would use the index, but if instead you said 'Status' = 'Complete' for another query, then there is absolutely NO reason that the DB would use that particular index because it'll be just about as quick to do a complete table scan as it would be to use the index, so you likely wouldn't be of any use....this is why clustered indexes are so useful, and why there is a clustered index on C1 by default. A clustered index states not only are my values unique, but they are sorted. This is a MAJOR difference between a clustered and non clustered indexes...the sorting. In a clustered index search the DB knows that when it reaches the value/range it needs, it can stop scanning the index. I hope this has answered most/all the questions you had :) -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Pruitt, Christopher (Bank of America Account) Sent: Wednesday, April 13, 2011 12:12 PM To: [email protected] 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 [email protected] 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:[email protected]] On Behalf Of LJ LongWing Sent: Wednesday, April 13, 2011 12:31 PM To: [email protected] 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:[email protected]] On Behalf Of Pruitt, Christopher (Bank of America Account) Sent: Wednesday, April 13, 2011 10:52 AM To: [email protected] 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 [email protected] 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"
