HOW A GOOD INDEX STRATEGY CAN HELP POLICE SUCCESS
As SQL developers, we are usually focused on creating queries which return the correct results. This is a good principle. However, there are more things to take into consideration when you create a query. One of them is the query performance. You think that query response time is not essential.? In this article, we will cover one example where query response time is critical.
SCENE ONE: 911 CALL CENTER
Let’s suppose we are at the 911 call center when the phone rings. One of the operators answers the call, it is a witness reporting a man has been shot. After the emergency unit is dispatched, the phone operator continues talking in order to identify the potential killer. The witness describes the aggressor as a Caucasian man, age about 30 and 40 and leaving fast from the crime scene in a green Ford car. The following dialog was maintained between the 911 operator and the witness.
911 operator: Could you tell me the plate number?
Witness: Not entirely, but I can saw the plate number ending in 3581, and having an H in the three character part of the plate. However, I’m not sure about the exact place where the H was.
911 operator: Do you remember any other thing?
Witness: No, unfortunately not
911 operator: Thanks for your help, we will call the police right now.
SCENE TWO: THE POLICE STATION
Now suppose you are an information technology police officer specialized in SQL. The call received about the killer was passed to the police station, and you need to act fast. You need to create a query to obtain possible identification data about the killer. You have access to a national database with information about all the country citizens and vehicles. Let’s start right now; you have the following two tables:
Our first try is oriented to identify the car; then as we have the partial plate number, we will try a query like:
However, when we executed this query, the reply never came up. Each second is important, so let’s review some tips to accelerate the query. The reason is: the vehicles table have more than 70 million records, and the existing indexes on the table cannot be used in this query. Then, the database engines have to do a full sequential scan on the table vehicles and check condition (Green, Ford, and plate_no) on each record.
ADDING A FUNCTIONAL INDEX
Usually, the key of an index is a column of the table or a composite key formed by more than one column. Fortunately exists another kind of indexes, where the key of the index is not a simple column. This index is called functional indexes, and the key is the result to apply a function to a column (or columns). Then if we use the same function as the search condition in the where clause of the query, the functional index can be used. This is exactly what we are going to do, to accelerate our query.
Let’s see a simple example; we have a table person (id int, last name text). We also have an index by last name. Let’s analyze which selects use the index:
The third row in the previous table has a ‘%’ at the first position of the string and due to this fact, the database engine can’t use the index. In our previous query on vehicles table, with the incomplete plate number, we are in the same situation as with the LIKE ‘%llinghurst’. To have index access, we will create a functional index using the substring function. Let’s try it.
Once the index is created, we can execute this query to obtain the vehicles we are looking for, and query performance will be fast because it will use an index previously created.
The previous image shows the EXPLAIN output, where we can verify the use of the functional index at the bottom of the image.
Improving performance is a challenge activity for SQL developers. Not all the slow queries can be improved. In some cases, the base tables are really big, and nothing can be done to improve. However, there are several techniques to apply, which in many cases, can help to accelerate query response time. In the next articles we will continue covering other approaches or techniques to accelerate query response time.