hi everyone and welcome to part two in this series on indexing in servicenow in part one we introduced the concept of indexes as well as provided a simple demonstration of how indexes can significantly improve query times in this video we're going to delve a little bit deeper into the topic so we're going to look at three main things here firstly the slow queries log this will enable us to determine for which queries we need an index for second we're going to examine the explain plan so that means we're going to take a closer look at the query itself to determine if an index was used if so which one and also what part of the index was used and third and lastly we're going to take a look at composite indexes in a little bit more detail so in the previous video we started by creating an index on our vehicles table which resulted in a significant Improvement in query times from around two and a half seconds down to a fraction of a second okay so the next question is how do I know when I need to create an index well for our example that we've just been dealing with a custom table in servicenow you will definitely need indexes for those you'll need to identify what queries are going to be performed most often against that table whether that is from user transactions you know list forms whether it is from background processes such as flows scheduled jobs Etc and look at the fields that are being used in those queries and create indexes based on them for system administrators you'll need to monitor the overall performance of your instance and one of the ways in which you can do that is by coming to the slow queries module here this table right here this table will record every query in the system where the total execution time for every query every similar query exceeds five seconds okay so when looking at this list we have to kind of remind ourselves well what do I actually mean by a slow transaction here is it a transaction that has maybe just occurred once or twice in a week where the execution of the query time was 10 15 20 seconds or is it more that we're looking at queries that are being performed more often where the total execution time is having a more of an impact on our database and our applications so the ladder is actually the case so for those individual transactions that are taking a long time to query maybe as a result of some report that you've got querying thousands and thousands of Records if it's only being performed once or twice a week who cares we could probably live with that if that query also is being performed you know late at night that's having a less of an impact on the entire instance we don't really need to worry about those transactions uh those queries rather but again for queries that are being performed more often where the average execution time perhaps is half a second one second or something like that and that that may potentially have more of an impact on your application so these are the ones that we need to look at so this is why we have both of the columns here in this table at execution time for individual transactions Plus or multiplied by the execution count should equal the total execution time okay so if we were to filter this list and search for things with our vehicles table in the query itself should find quite a few examples here um many as a result of previous testing that I performed with this table but I think this second one here the average execution time too I'm not sure if that is with an index or not but we can see here we've got the SQL query right here and you can see the where statement uh there showing where the make is Volvo so that's actually the one that we probably just performed all were performing both maybe before and after an index was created and if you go a little bit further across here under example URL that's also a helpful feel to understand to decipher what's being recorded in this table everything with a name in it here like a kind of a regular name here without the Slash and kind of URL format here they usually indicate background jobs so scheduled jobs that are being executed and everything with a slash at the beginning of it usually indicates that it's a user transaction that's been performed okay one of the things that you can do is actually dive into the slow query record itself to see some more details and one of the things that you can get from here I won't go through everything in here uh is an explain plan okay so for those of you who may be a little bit more familiar with mySQL databases you'll know what explain plan they're basically a record of information about the query that's been performed what index has been used and so forth so if we after clicking on that button you'll see here those related lists kind of pushed over to the side here but if you open up this record okay so without going through all the details of this record again there is documentation on the MySQL website about explain plans I'll put a link in the description below but some things to look out for uh in the uh extra field here using index is actually a good sign because they're using an index uh here we've got the possible keys of possible indexes now at the moment we only had one which was based on the make okay so that was the only one really that was selected and we've got the table that was queried and this is also a good sign here that an index was used here this is the number of rows that were queried while using that index to locate a record so all that is looking quite good okay so what I'd like to do now I am going to come back to Studio here so uh I just did a pause in the recording and created a separate index here based on the country and the year okay and we'll just do some more testing and in doing so I just want to explain a few more points about indexing that I think you should be aware of just some fundamental knowledge about how indexes work okay so importantly here I've got for all my indexes the two that I've created I've got more than one column in H1 okay you don't need to create individual indexes for every single column in your table that may actually defeat the purpose of speeding up your queries so for the make model and City query that we created first of all that is valid for not just queries based on the make but also based on the make and the model as well as make model and City okay all those three queries will benefit from that index so let's go back to our background processing here and I'm just going to modify this query here at the moment it's just based on the make and I'm going to put in that there the model as well okay it's an encoded query and we'll just run that okay so we can see here that was also extremely fast only eight milliseconds okay so if we were to come back and make another modification to this query and add the year as well or rather the city where the city is I'm not sure what ones we've got but I'll just put in Orlando and we'll run that okay you can see that's also relatively quick 233 milliseconds we'll just do maybe one more test there for that same query yeah so you can see it's now down to six milliseconds okay so again all of those three separate queries benefit from that same index now let's do another test let's come back to our query here I am going to remove everything here except for the model we'll just do a query on the model only okay so let's remove that and run that script okay so as you can see that was a little bit higher 815 milliseconds let's do one more test run it once more okay again nearly 800 milliseconds so definitely a lot slower than the six milliseconds that we're getting before and that's because in this case the index is not being used the model field is in the index but because the make field was the first element first part of that index that actually has to be the first part of your query as well for that index to work in general okay so let's do another test uh we've got the model here I'm going to add the make here after the model but they're both a quality operators so I think in this case I'd have to double check but I think in this case because they're both the quality operators and they're both in the index the order in this case I don't think matters so let's run it yeah so you can see here the query duration was only eight milliseconds so that's definitely an indication that an index was used in this case all right let's do another test uh let's come back here I'm going to make um the make again the first part of that query and I'm going to add the model here once more [Music] XC 60 but this time I'm going to make the operator here greater than or equal to Vol so that would potentially get us Volvo and Volkswagen makes here okay so make and model all right let's try that okay that was a little bit higher 60 milliseconds let's come back and try it again okay 53 milliseconds okay not too bad okay but I think what's Happening Here is and maybe we can check the explain plan is that because this is a range operator here in the first part the index index will actually well only that first part of that index will be used okay so I'll actually stop here if it was like this like we originally had it then it would use the make index we use the model index and if we had city as well it would use the city as well okay but because we have a range here this is kind of where the index stops if you like so we use the make part of the index but not the model okay so to finish off I've got one nugget of information to share with you a little tip so I'm going to come back to my explain plan here that we looked at earlier and then just come back to the slow query transaction that was linked to it okay at the moment you can only get an explain plan for queries that are located in the slow query table okay so if you want to test new queries or whatever that may be not necessarily registered in slow query table then you won't be able to get an explain plan from them however there is a workaround okay so you can see here in the slow query record we have the SQL statement right here so we've got that original one for make is equal to Volvo there okay so what I can do I can actually just copy that let me go back to the query list and I'm going to go ahead and create a new record I can actually create a slow query record in servicenow [Music] so now what I can do I can just paste that query right in there and I can write any query that I want to I can actually and maybe just do the following I'm just going to do that where make is Volvo and where the model is XC 60. and say that because once I have this record in here I can actually create an explain plan based on this query and I can see okay what is the system going to do is it going to use an index if so which index what part of the index and so forth and it's confirm that my indexing that I've created is actually working so let's go ahead and click on explain plan thank you and again we'll come down to the and open that here okay so we can see here that we were querying uh this vehicle table here the possible keys here that were evaluated were just one make even though we had a second index created as well that was based on the country so probably wasn't even considered uh and we used two parts of the index here this is what this refers to because we were occurring based on the make and the model if we also query based on the city then we'll have another const value here as well to indicate that three parts of that index were used but in this case two parts were used and the roads that were returned based on that index were 338. okay with that query so if we actually go to the list here and if I just add XC60 in here we've got 338 records returned okay so this explain plan will help you evaluate what index was being used how many records were returned using that index or scan using that index what part of the index was used and so forth okay so that was a lot of work a lot of testing but I hope now you've got a basic understanding of how indexes work in general but also in particular in servicenow and as I said in the introduction if you are a developer or an administrator of the now platform then you'll need to have an understanding of how indexes work because queries are going to be made against your tables all the time and you need to make sure that your applications that you're developing or that you're administering are working and Performing as they should so I hope this video has been helpful for you again this is just a starting point there are many resources out there that you can use to learn more about indexing I've put some links to the ones that I found helpful down below and we'll see you next time bye for now foreign [Music]