How does index creation helps improving performance?

[antonymirza]

I heard many times for for creating index for tables and fields to improve system performance. When I check in wiki it explains indexing a field set it to store unique values therefore avoid duplicate entries but not sure how does index creation helps in this regard.

Can anyone please explain?

Brgds, AM

[Pradeep Sharma]

Hi Antony,

Yes there is not much details on wiki for this.

Please go through the below links for further info on this.

Good Indexing May Improve Database Performance - TechRepublic

performance - Database indexing - how does it work? - Stack Overflow

I hope this helps

Thanks,

Pradeep Sharma

[tltoulson · Accepted solution]

Hi Antony,

To add to the answer provided by Pradeep Sharma, here is a simple nontechnical description.   An index improves performance usually in two ways:

1.   Reduces the number of items the database has to search (does no apply to unique index, only non unique)

2.   Orders the indexed items to make it easier to intelligently skip searching records

To illustrate the first, imagine you have a list of 100,000 words in no particular order and you have to find all instances of a single word.   You would have to search all 100,000 words to find them all.   But, lets say that when you   remove the duplicates, there are only 10,000 words.   You could create an index of 10,000 entries where every word was unique and each unique word told you all the positions of that word in the original list.   Now you can find all instances of a word in the original list by searching through only 10,000 words in the index.

To illustrate the second, think of a dictionary (a real world index).   If a dictionary were unordered, you would have to search every single page to find a word (technically a dictionary is a unique index as well, imagine multiple definitions for a word listed separately and out of order).   But a dictionary indexes definitions by ordering words alphabetically.   This allows you to intelligently skip entire pages of words to find the word you are looking up.   So instead of searching through potentially millions of words, you can consistently eliminate large sections of words by skipping to a certain letter in the alphabet.

You can find more technical descriptions but the basic idea from a technical standpoint is that indexes use unique keys and applies different types of algorithms with different benefits to reduce the number of entries that need to be searched to find an answer from all of the records which is described as O(n) to some statistically improved number of records such as O(log n).

[SandySeegers]

THis discussion could not be more timely for me because I have been contemplating requesting a new index on task.   But now I'm not entirely sure it would help.

We have implemented a rudimentary row level access scenario that involves a new field on task that is populated for a subset of users.   That field is then used in before query business rules and in ACLs to limit the view of those tickets to a limited audience.   There are only two possible values the column.

After reading your response and scanning one of the included reference links, I'm getting a sense that an index on our row access field may not be that helpful because it's limited to just a few values?   Or is there still value in adding an index because I'm using it to filter results in some before query business rules?

Thanx for you thoughts in advance!

Sandy

[tltoulson]

Hi Sandy,

In your given scenario the index is unlikely to help.   Business Rules and ACLs are applied after the query and an index will only improve speeds of the query itself.   That said, if you move the logic into the GlideRecord query itself, the index could improve search speeds depending on the distribution of values and the frequency of search.

Think of it this way, if half of your records are populated and half are not, every time you query that field you eliminate 50% of the records that need to be scanned by using an index regardless of which value you search.

On the flip side, if 10% are populated and 90% are not, and you predominantly search within those that are not populated, your index would only eliminate 10% of the rows to search.

But again, this will only work if you change your GlideRecord query.   The index will have no impact on ACLs or Business Rules (at least to my knowledge).