Understanding Database Indexes in ServiceNow

ServiceNow uses the MySQL database to manage its information, so whenever users are looking at a list of records they’re looking at the results of a database query.
Like all databases, MySQL is designed to handle large volumes of information in the most efficient manner possible. To do this, MySQL has a concept known as indexes. These work very much like the index in the back of a book. Instead of flipping through every page looking for something, a quick trip to the index can let you find all the references for a subject in one spot. In the same way, table indexes increase the speed of database queries.
Real world example
Here’s a real world example of a user waiting upwards of a hundred seconds for the following query on a table with 200,000 rows.
On investigation, it became apparent that there was a database index that should cover this scenario, but it wasn’t being automatically picked up by ServiceNow because of the way indexes work.
Indexes are ONLY used if the columns within them are used seqentially, and this is an important point to understand.
Understanding index order
It doesn’t matter which order the columns are used within the query itself, but they MUST occur based on the order in which they have been put into the index as read from left to right. For example, when it comes to this particular index, the following holds true.
Also, it’s important to note that key columns (like Assignment Group) probably occur in several indexes, so just because this particular index isn’t used doesn’t mean no index will be used. Another index might pick up where this one leaves off.
When a query or report is run, the database engine will examine all the indexes on the table to determine which is most efficient index to use.
All the reference fields used in ServiceNow have an index applied to them, but these aren’t always the most efficient way to query the database. For example, consider these two indexes.
- Assigned To, Active
- Active, Assignment Group, Assigned To, Number
The first index is the default index for the Assigned To column as a reference field. Although it filters on Active, that is only applied AFTER the Assigned To.
The second index is the one we’re examining, but it can only be used if Assignment Group is also used (following the order of the index from left to right).
If you have a table with a million rows but only 20,000 of them are active, then which of these approaches will be more effective?
a) Select EVERY entry assigned to a person and only then filter on active records
b) Select ONLY active records and then filter on that person
As 98% of the records are inactive, option (b) will produce the best results as it will completely ignore 980,000 records before it starts filtering by name.
So in this case, querying by Active, Assignment Group and Assigned To will be considerably quicker than querying by Assigned To and then Active.
Going back to our real world example, we can see that our index is NOT being used because the very first column in the index hasn’t been included so none of the other parts of the index are being applied. By adding Active we’ll bring this index into play.
And the results?
We’ve gone from 100 seconds down to less than one second!
In this particular case, the results were exactly the same as this person was only actually interested in active records anyway but forgot to include Active in their query, but what if we wanted both active and inactive results? Ah, this is where it gets interesting…
Again, by including Active we’re allowing the MySQL database to use this index and improve performance, and look at the results.
Rather than waiting almost two minutes, we have our results in under a second.
In theory, this query is now exactly the same as the original (which didn’t specify whether a record was active or not) and yet look at how much faster it is because the database can use this index.
Also, it’s interesting to note, that in the absence of this index, the database would have used index (2) above, but because active was applied second it was grossly inefficient.
Trying this same approach on a table with 2.7 million rows, retrieving ALL the rows (with no query) took 21 seconds. Using the seemingly counterintuitive approach of "active=true or active = false" consistently reduced that to 13 seconds, and still returned all 2.7 million rows! That’s a reduction of 40% in the query time!
JDS doesn’t recommend using this "active=true or active=false" query. It’s included only as an example to help make the point. All list views should have a query behind them, and those queries should be based on the underlying database indexes.
The lessons from this are...
- Don’t underestimate the importance of your database indexes.
- Be sure to conduct an audit of your commonly used application modules and slow running transactions to make sure the queries in them leverage database indexes properly.
Find out more
To learn more about how JDS can optimize the performance of ServiceNow, contact our team today on 1300 780 432, or email [email protected].
Our team on the case
Our ServiceNow stories

ServiceNow & ReactJS
Like any enterprise platform, ServiceNow has a complex relationship with its underlying architecture. Originally, ServiceNow was built on Java ...
Read More

ServiceNow Safe Workplace Suite
Read More

Working With ACLs In ServiceNow
Read More

How ServiceNow’s ‘Virtual Agent’ can assist your organisation: Part 4
Read More

How ServiceNow’s ‘Virtual Agent’ can assist your organisation: Part 3
Read More

How ServiceNow’s ‘Virtual Agent’ can assist your organisation: Part 2
Read More

How ServiceNow’s ‘Virtual Agent’ can assist your organisation: Part 1
Read More

How Field Service Management can help your customers
Read More

ServiceNow Upgrade Process
Read More

Modifying Service Portal Widgets On-The-Fly
Read More

Custom Glide Modal Dialog Boxes in ServiceNow
Read More

How Contract Management Can Help Your Customers
Read More

Manipulating Service Portal Widgets Without Modifying Them
Read More

Virtual Agent Is Your Friend
Read More

Using Common Functions in the Service Catalog
Read More

ServiceNow Archiving
Read More

Browser Console
Read More

Glide Variables
Read More

Asset Management in ServiceNow
Read More

Understanding Database Indexes in ServiceNow
Read More

Fast-track ServiceNow upgrades with Automated Testing Framework (ATF)
Read More

ServiceNow Catalog Client Scripts: G_Form Clear Values
Read More

How to effectively manage your CMDB in ServiceNow
Read More

Breaking down silos to create an enterprise capability
Read More

ServiceNow and single sign-on
Read More

How to customise the ServiceNow Service Portal
Read More

Integrating a hand-signed signature to an Incident Form in ServiceNow
Read More

Integrating OMi (Operations Manager i) with ServiceNow
Read More

Service portal simplicity
Read More

Filtered Reference Fields in ServiceNow
Read More

ServiceNow performance testing tips
Read More

Straight-Through Processing with ServiceNow
Read More

ServiceNow Choice List Dependencies
Read More