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.
Introduction to the SQLite expression-based index
INDEX in SQLite is a table-like structure that holds the name of the row and the row ID of a table, not the entire rows and columns. The index is a lightweight table, which helps the developers to improve the performance of the Table. INDEX helps in a faster and smoother data-retrieval process. Creating an INDEX of a SQLite table where data retrieval is done frequently is always recommended. Expression-based INDEX in SQLite improves the performance of the complex SQLite statements that use any Expressions.
The main difference between the normal SQLite INDEX and Expression-Based INDEX is that the Expression-Based INDEX allows the developer to create an INDEX based on the outcome or result of an Expression provided in the Statement.
The Expressions can be applied to only one or many columns of a certain table. Expression-based INDEX is mostly useful when it comes to complex SQLite statements which consist of some expressions on one or more than one column.
Syntax:
CREATE INDEX <index_name> ON <table_name> [EXPRESSION(<column_name>)];
Explanation: The above syntax explains that the syntax is pretty much simple like the normal SQLite INDEX. But the change is that after the <table_name> we have to provide the Expression based on which the INDEX will be further signified.
Prerequisite
Users must be familiar with SQLite INDEX and the basic commands of SQLite such as INSERT, CREATE etc.
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));
Creation of Index
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 Restriction
Although Expression-Based INDEX is useful for SQLite and increase the speed of data retrieval process, there are certain restrictions which comes with it’s benefits. Those are listed below:
- Proper Reference: The columns passed as the Argument of the Expression used, should be associated with the Table itself on which the INDEX is being made. So before passing the argument of the Expression, it is recommended to re-check and validate the name of the Columns passed.
- Increased Complexity: Use of Expression Based INDEX increases the complexity of SQLite statement and makes it hard to understand for SQLite Programmers. Only the experienced SQLite programmers can handle and use the Expression–Based INDEX.
- Deterministic Function: The Expressions which can be used in this type of Indexing should be Deterministic in nature.
- Doesn’t work with Subquery: The expressions doesn’t work with a Sub-query.
Conclusion
As we saw in this article, how the Expression Based INDEX differs from that of the normal SQLite INDEX. We also saw when and how to use it, the benefits of using it and also the restrictions of using Expression-Based INDEX. Althought this can be very useful when it comes to increase the speed of data retrieval process, it makes the entire SQLite statement a little-bit complex and hard to understand, especially for those who are new to SQLite.
Also, the chance of making error increases while creating this type of INDEX. If the developer is not well-versed with the SQLite and the Expression-Based INDEX then they might fetch the wrong columns and pass it to the Expression which might eventually cause a serious issue.