SQLite

SQLite Expression Based Index

In the SQLite or SQL databases, indexes are used to reference some columns of a table. These indexes can be composed of one or more columns and can be unique. Along with that, we can create indexes on different expressions containing different columns of any table. These expressions could be defined in the WHERE condition clause of the SELECT instruction. Thus, we decided to explain the usage of expressions to create indexes and how they affect the results.

Let’s get started with this article by opening the shell terminal of Ubuntu 20.04 quickly. Use the shortcut key “Ctrl+Alt+T” to do so. After the successful launch of the shell terminal, you need to update your Linux system by the use of update and upgrade commands with the help of an apt package. After that, open the SQLite database using the “sqlite3” instruction. Right now, we have no tables in the database so far, i.e. using the .tables command.

To create the indexes on expressions, we need a table in our database. We created a new table named “DETAIL” in our database containing a total of 3 integer type columns, i.e. ID, RETAIL, and SALE. The table is empty right now, i.e. as per the SELECT command.

CREATE TABLE DETAIL(ID INT PRIMARY KEY NOT NULL, RETAIL INT, SALE INT);
SELECT * FROM DETAIL;

We have to add some records in the DETAIL table so that we can create the indexes on the columns after that. Thus, we utilized the INSERT instruction with the INTO keyword followed by the table name “DETAIL” and its column names “ID, RETAIL, SALE”. The column names are followed by the keyword VALUES and the records to be inserted in the columns. We inserted a total of 10 records within three columns of this table; each one is unique as shown:

INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (1, 340, 400);
INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (2, 380, 420);
INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (3, 205, 230);
INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (4, 98, 110);
INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (5, 311, 340);
INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (6, 100, 120);
INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (7, 512, 540);
INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (8, 634, 670);
INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (9, 54, 67);
INSERT INTO DETAIL(ID, RETAIL, SALE) VALUES (10, 934, 990);

After using the SELECT instruction with the “*” for displaying all the column records from the DETAIL table, we showed the newly inserted 10 records in the table.

SELECT * FROM DETAIL;

Before creating an index on the expressions, we take a look at the simple query to fetch the records with the use of expressions. Also, we utilize the ORDER BY option within the query to organize the results according to the descending order of a length of a column RETAIL. A total of 8 records are displayed on our screen so far.

SELECT * FROM DETAIL WHERE LENGTH(RETAIL) > 2 ORDER BY LENGTH(RETAIL) DESC;

We use the same query within the SQLite query planner instruction to see how the SQLite works for executing this query. We found that it has to scan the whole table “DETAIL” using the temporary B-tree (balanced tree) on the ORDER BY clause.

EXPLAIN QUERY PLAN SELECT * FROM DETAIL WHERE length(RETAIL) > 2 ORDER BY length(RETAIL) DESC;

Let’s create an index on the same expression “Length” on the RETAIL column of a table DETAIL. We utilize the CREATE INDEX instruction to create the index “index_retail”.

CREATE INDEX index_retail ON DETAIL(LENGTH(RETAIL));

After creating the index on the DETAIL table, we used the query planner to see the working structure of the query. It shows that the query has been working on the expression-based index to fetch the required results instead of searching the whole table.

EXPLAIN QUERY PLAN SELECT * FROM DETAIL WHERE LENGTH(RETAIL) > 2 ORDER BY LENGTH(RETAIL) DESC;

After using the SELECT instruction on the same expression to fetch the records, the data is now displayed in the descending order of length of the RETAIL column.

Let’s create another SELECT query with an expression. This time, the WHERE class expression will only display the records where the sum of the RETAIL and SALE column is greater than 700.

SELECT * FROM DETAIL WHERE RETAIL+SALE>700;

We utilized the previous SELECT instruction along with the single columns and the expression “RETAIL+SALE>700” it contains, in the query planner instruction. The query planner result shows that the SQLite database has to search all over the DETAIL table to fetch the required results with this quite inefficient expression.

EXPLAIN QUERY PLAN SELECT ID, RETAIL, SALE, RETAIL+SALE FROM DETAIL WHERE RETAIL+SALE>700;

Let’s create a new index on the expression “RETAIL+SALE” that is used in the previous SELECT instruction. So, we utilized the CREATE INDEX instruction to generate a new index “index_detail” on the expression “RETAIL+SALE” of a DETAIL table. The newly made index “index_detail” can be seen within the indices list as per the “.indices DETAIL” instruction.

CREATE INDEX index_detail ON DETAIL(RETAIL+SALE);

Now, let’s try out the query planner instruction for the same SELECT instruction containing the RETAIL+SALE>700 expressions within it that is used in the WHERE clause of the SELECT instruction. After using the query planner, we found that the SQLite plan will only search the specific entries of a table DETAIL as per the index “index_detail” containing an expression.

EXPLAIN QUERY PLAN SELECT ID, RETAIL, SALE, RETAIL+SALE FROM DETAIL WHERE RETAIL+SALE>700;

Let’s modify the expression by exchanging the names of the columns with each other without changing their knowledge. Whenever you use the same SELECT instruction with the little change in its expression, i.e. “SALE+RETAIL>700”, it displays the same 5 results since the logic is correct and the same is used in the previous SELECT instruction.

SELECT * FROM DETAIL WHERE SALE+RETAIL>700;

After using the previously modified SELECT instruction within the query planner instruction, we found that the SQLite database has to search the whole table “DETAIL” for this expression-based query. This is because we haven’t created an index for the newly modified expression “SALE+RETAIL>700” in the SELECT query.

EXPLAIN QUERY PLAN SELECT ID, RETAIL, SALE, SALE+RETAIL FROM DETAIL WHERE SALE+RETAIL>700;

Conclusion

This article is all about creating an index on different expressions. We tried to use the query planner instruction to explain the difference between using a simple expression within a  SELECT instruction without any index and the use of an expression within a SELECT instruction based on an index.

About the author

Omar Farooq

Hello Readers, I am Omar and I have been writing technical articles from last decade. You can check out my writing pieces.