How does index creation helps improving performance?

antonymirza · ‎07-07-2014

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

tltoulson · ‎07-07-2014

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).

View solution in original post

SandySeegers · ‎07-14-2014

Thanx for the speedy reply, Travis.

I get what you're saying about ACLs, but I was under the impression that before query business rules impact the building of the query before it happens.

My before query business rule checks to see who the user is. If they have a particular role, I user an addQUery statement to include a check for a specific value in my row access field. So the sql call includes:

WHERE u_row_access IS 'text string'

So given that, instead of returning 1 million records, I only return 10000 records from the query?

(it's late. My head is about to explode. I hope this makes sense)

Thanx for listening.

Sandy

tltoulson · ‎07-14-2014

Hi Sandy,

Yes yes! You are correct. Sorry, I forgot about the before query rules. Yes, those will apply to the query itself so normal indexing guidelines apply.

So indexing your field should improve performance according to how many records it can ignore based on the index. So in your example, if 10,000 records have the string value and the rest of the million records do not, your index should substantially reduce the time to query those 10,000. But it won't do much for the inverse query (u_row_access IS NOT 'text string')

SandySeegers · ‎07-15-2014

Thanx Travis! Your feedback is helpful.

I have another question about the ordering of filtering criteria vis-a-vis the behind-the-scenes build of the SQL command. But I think I'll start a new thread for that.

Thanx again.

antonymirza · ‎07-08-2014

Hi Pradeep and Travis,

Thank you for your sharing and explanations. I am clear with this concept now and Index creation makes more sense to me.

Brgds, AM

SaschaWildgrube · ‎01-31-2022

The DevTools scoped app contains a number of helpful re-useable scripts.

One of them can create database indexes via scripts - e.g. as part of an installation script.

Check it out here:

https://github.com/saschawildgrube/servicenow-devtools/blob/master/update/sys_script_include_55cccc6...