How Expression-Based Index Work?
For this example, we will be using a table called Students which is already being populated with values. We will create an Expression-Based INDEX over that table and use one of it’s column by passing it inside a function.
Query:
create index idx_students on students(lower(firstname));
Explanation: Here, the INDEX idx_students has been made on the Table Students and for the Expression, the function LOWER() has been used over the FirstName. This changes all the firstname of the students into lower case letters. This INDEX will get invoked and ease the data retrieval process when there is a query which asks for the FirstName of all the students in the Lower Case.
Apart from fetching each data one by one from the real database, then converting them into lower case and returning them, this INDEX will be invoked and return the lowered version of the FirstName of the students faster.
If we wants, they can also see if the INDEX has been created or not.
Query:
PRAGMA index_list('students');
Output:
Explanation: The above command returned a tabular output with various columns such as unique, origin and partial. Each of these columns were generated automatically after executing the command. The significance of them are below:
- unique: Unique column indicates whether the index is unique or not. If the value is 1 then the corresponding Index is unique, if 0, then the index is not unique.
- origin: This indicates the origin of the index, it signifies that the index is explicitly created by the User/Developer or is it a Primary key or Unique constraint. Here in this case the value “c” signifies that it has been created by the user.
- partial: This indicates whether the index is partial or not, partial indexes include a subset of rows based on certain conditions provided. If the value is 1, then the index is partial, if the value is 0 then the index is not partial.
SQLite Expression Based Index
SQLite is an embedded database that doesn’t use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. The main features of SQLite are that it is a server-less, self-contained, reliable, full-featured SQL database engine.
In this article, we will look into how an Expression-based Index works and how it speeds up the data retrieval process for queries that use Expressions.