Mastering in SQLite

Index and Expression-based Index

Before getting into Index and Expression- based Index ,if you haven’t covered the previous topic- views, then get into it.

To visit the introduction page to see the available topics click here.

A number of superheroes are available in our DB, click here to download it.

Index

In relational databases, a table is a list of rows. In the same time, each row has the same column structure that consists of cells. Each row also has a consecutive rowid sequence number used to identify the row. Therefore, you can consider a table as a list of pairs: (rowid, row).

SQLite uses B-tree for organizing indexes. Note that B stands for balanced, B-tree is a balanced tree, not a binary tree.

Each index must be associated with a specific table. An index consists of one or more columns, but all columns of an index must be in the same table. A table may have multiple indexes.

To create an index, you specify three important information:

• The name of the index after the CREATE INDEX keywords.
• The name of the table to the index belongs.
• A list of columns of the index.

Syntax

CREATE [UNIQUE] INDEX index_name  
ON table_name(column_list);

To create an Index,

CREATE INDEX idx_name  
ON movieDetails(movieName);

To check if SQLite uses the index or not, you use the EXPLAIN QUERY PLAN statement as follows:

EXPLAIN QUERY PLAN  
SELECT
      movieName
FROM
      movieDetails;

To create a unique index just add the keyword UNIQUE as CREATE UNIQUE INDEX .

To find all indexes associated with a table, you use the following command:

PRAGMA index_list('movieDetails');

To drop an index from a database, use the DROP INDEX statement as

DROP INDEX idx_name;

Expression-based Index

When you create an index, 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.

To create an index based on the expression,

CREATE INDEX idx_name 
ON charactersDetails(LENGTH(salary));

The following lists all the restrictions on the expression that appears in the CREATE INDEX statement.

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.
2. The expression can only use the deterministic function call.
3. The expression cannot use a subquery.

Reach out the next topic-Trigger

Sridharan T