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.

1. Assigned To, Active
2. 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.