{"id":1156,"date":"2016-05-30T21:50:53","date_gmt":"2016-05-30T14:50:53","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1156"},"modified":"2022-04-03T15:08:16","modified_gmt":"2022-04-03T08:08:16","slug":"sqlite-index-expression","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-index-expression\/","title":{"rendered":"SQLite Expression-based Index"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite expression-based index to query data to improve the query performance especially for the queries that use expression or function.<\/p>\r\n\r\n\r\n\r\n

Introduction to the SQLite expression-based index<\/h2>\r\n\r\n\r\n\r\n

When you create an index<\/a>, you often use one or more columns in a table. Besides the normal indexes, SQLite allows you to form an index based on expressions involved table columns. This kind of index is called an expression based index.<\/p>\r\n\r\n\r\n\r\n

The following query selects the customers whose the length of the company is greater than 10 characters.<\/p>\r\n\r\n\r\n

SELECT<\/span> customerid,\r\n       company\r\n  FROM<\/span> customers\r\n WHERE<\/span> length<\/span>(company) > 10<\/span>\r\n ORDER<\/span> BY<\/span> length<\/span>(company) DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
If you use the EXPLAIN QUERY PLAN<\/code> statement, you will find that SQLite query planner had to scan the whole customers<\/code> table to return the result set.<\/p>\r\n\r\n\r\n
EXPLAIN<\/span> QUERY<\/span> PLAN\r\nSELECT<\/span> customerid,\r\n       company\r\n  FROM<\/span> customers\r\n WHERE<\/span> length<\/span>(company) > 10<\/span>\r\n ORDER<\/span> BY<\/span> length<\/span>(company) DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The SQLite query planner is a software component that determines the best algorithm or query plan to execute an SQL statement. As of SQLite version 3.8.0, the query planner component was rewritten to run faster and generate better query plans. The rewrite is known as the next generation query planner or NGQP.<\/p>\r\n\r\n\r\n\r\n
To create an index based on the expression LENGTH(company)<\/code>, you use the following statement.<\/p>\r\n\r\n\r\n
CREATE<\/span> INDEX<\/span> customers_length_company \r\nON<\/span> customers(LENGTH<\/span>(company));<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Now if you execute the query above again, SQLite will use the expression index to search to select the data, which is faster.<\/p>\r\n\r\n\r\n\r\n
How the SQLite expression-based index work<\/h2>\r\n\r\n\r\n\r\n
The SQLite query planner uses the expression-based index only when the expression, which you specified in the CREATE INDEX<\/code> statement, appears the same as in the WHERE<\/a> clause or ORDER BY<\/a> clause.<\/p>\r\n\r\n\r\n\r\n
For example, in the sample database<\/a>, we have the invoice_items<\/code> table.<\/p>\r\n\r\n\r\n\r\n
The following statement creates an index using the unit price and quantity columns.<\/p>\r\n\r\n\r\n
CREATE<\/span> INDEX<\/span> invoice_line_amount \r\nON<\/span> invoice_items(unitprice*quantity);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
However, when you run the following query:<\/p>\r\n\r\n\r\n
EXPLAIN<\/span> QUERY<\/span> PLAN \r\nSELECT<\/span> invoicelineid,\r\n       invoiceid, \r\n       unitprice*quantity\r\nFROM<\/span> invoice_items\r\nWHERE<\/span> quantity*unitprice > 10<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The SQLite query planner did not use the index because the expression in the CREATE INDEX<\/code> ( unitprice*quantity<\/code>) is not the same as the one in the WHERE<\/code> clause (quantity*unitprice)<\/p>\r\n\r\n\r\n\r\n
SQLite expression based index restriction<\/h2>\r\n\r\n\r\n\r\n
The following lists all the restrictions on the expression that appears in the CREATE INDEX<\/code> statement.<\/p>\r\n\r\n\r\n\r\n
  1. The expression must refer to the columns of the table that is being indexed only. It cannot refer to the columns of other tables.<\/li>
  2. The expression can only use the deterministic function<\/a> call.<\/li>
  3. The expression cannot use a subquery.<\/li><\/ol>\r\n\r\n\r\n\r\n
    In this tutorial, you have learned how to use the SQLite expression based index to improve the query performance.<\/p>\r\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t