SQLite Indexes

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 quick, self-contained, reliable, full-featured SQL database engine.

The index 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.

In this article, we will see how Indexes can be used in SQLite, how to create an Index, how to Drop an index, and so on.

Index in SQLite

As the name itself suggests, the word “index” means contents. a list that consists of the titles and their page number. Similarly, in SQLite, the Index is a schema object that holds just the name of the row and the row ID.

It helps the developers speed up the data retrieval process using the SELECT and WHERE commands, but the drawback of an Index is that it slows down the Data INPUT Process, which is done by the UPDATE or INSERT commands.

Indexes should only be used when the table is Readonly and data will only be retrieved from it, not added.

How Does an Index Work?

Index works as a blueprint of a column or columns of a table with it’s corresponding row ID. Indexes are made to ease the data retrieval process from a table. The SELECT and WHERE statements work faster in case of Indexes than that of the entire actual table. Indexes uses a special data structure called as B-Tree to sort the column values.

B-Tree is used by Indexes because it a kind of data structure which only holds 2 things, a Key and the Address / Value. The key can refer to an address, or simply put it is an UNIQUE Constraint or value which can uniquely identify an address or a value corresponding to it.

The benefit of using B-Tree is that it allows the developers achieve faster searchers and data retrievals, also using Indexes take up a lot smaller space in the Storage area.

Also as the B-Tree automatically sorts the data, the searching and retrieval time becomes a lot less as compared to unsorted database.

SQLite CREATE INDEX Statement

In this section we will see how we can Create an SQLite INDEX using the CREATE INDEX command.

Syntax:

CREATE [UNIQUE INDEX | INDEX] index_name ON table_name(col1,col2,col3.......);

As we can see in the syntax, we can create 2 types of Index in SQLite:

1. NORMAL INDEX: It will create a simple Index on the table name mentioned over the columns mentioned in the parenthesis without considering any other criteria. This index can be created before or after the creation of the Table.
2. UNIQUE INDEX: Unique Index will not just create a simple INDEX but it will also take care of the fact that there is no two identical value present in the columns on which the Index has been created. It is recommended to create an UNIQUE INDEX before the creation of the Table, so that if the developer or anyone mistakenly provide duplicate value for a column, then SQLite will throw an error saying the value already exists.

After mentioning the type of INDEX, we will pass the Index Name, it can be anything as of the users’ choice. After than we will us ON keyword and then pass the Table_Name and in parenthesis we will provide the list of column(s) on which the index will be created.

For this, We will use an already created and populated table called Employees. Whose columns are empID,DeptID, FirstName, LastName, Salary, Location

Out of all this, the empID column only holds unique values. We will create an UNIQUE INDEX and a Normal INDEX over this Table. The contents of that table is given below –

After Inserting Some data into the employees table, The table looks:

Creating UNIQUE INDEX on Employees Table

We will create an UNIQUE INDEX on the Employees Table and consider the empID column as our base column. Then we will try to Insert a value to the Employees Table which has a duplicate empID.

Query:

CREATE UNIQUE INDEX Index_Employees_empID ON Employees(empID);

Explanation: We are using the CREATE UNIQUE INDEX command and then passing the Index name as Index_Employees_empID and then providing the table name and the column name in parenthesis. If written correctly, there will be no output or error displayed on the screen.

Next, we will try to insert a value in the Table which has the same empID.

Query:

 INSERT INTO Employees VALUES(1, 'D2', 'Arnold', 'Swift',25000, 'NY');
 INSERT INTO Employees VALUES(14, 'D2', 'Arnold', 'Swift',25000, 'NY');

Both the id 1 and 14 is already present in the table. SQLite will not let us insert these data and throw the below error –

Explanation: As we can clearly see in the output that SQLite is not allowing use to insert a value into the Employees table with the empID 1 and 14 as both the values exists already in the table. This is the advantage of using UNIQUE INDEX over the simple INDEX as it works as a security measure which restricts the user from entering duplicate values.

Creating INDEX on Employees Table

Here we will create a simple INDEX on the Employees table and consider the column FirstName as our target column. Write the below command.

Query:

CREATE INDEX idx_FirstName ON Employees(FirstName);

The above command will create an Index called idx_FirstName over the column FirstName.

SQLite Show Indexes

In this section, We will see how we can list all the Indexes we have created till now over a certain table. For that we will use the PRAGMA command.

Syntax:

PRAGMA index_list('table_name');

Here, we need to pass the table name between the inverted commas to display all the indexes created for that particular table. Write the below command to see the list of indexes created for the Table Employees.

Query:

PRAGMA index_list('Employees');

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.

Alternative Way to Display Indexes

Another way to display the list of indexes is to use the SQLite_Master table and provide the type as “index”.

Syntax:

SELECT type,name,tbl_name,sql FROM sqlite_master WHERE type = 'index';

Explanation: The above statement fetches the type which is Index, name which is the name of the Index, tbl_name which is the Table Name with which the index is associated with and SQL statement which was used to create the Index (users can skip this item if they don’t want to see the statement) from the SQLite_Master Database (It is the master database in which everything is stored) where the type is Index.

Output:

Explanation: This command displays the index alongside the table name and the statement used to create it.

SQLite DROP INDEX statement

In this section we will see how to DROP / DELETE any Index using the DROP INDEX command of SQLite.

Syntax:

DROP INDEX  <index_name>;

We will delete the Index_Employees_empID Index of the Employees table using the DROP INDEX command.

Query:

DROP INDEX Index_Employees_empID;

Then we will check if it has been deleted or not using the PRAGMA command.

Query:

PRAGMA index_list('Employees');

Output:

Explanation: As we can see, the Index Index_Employees_empID has been dropped successfully. The rest of the columns and their values are also gone.

Conclusion

In this article, We saw the importance of an INDEX, How to create an INDEX and how to DROP an INDEX using the various SQLite commands. Indexes are a very important part when it comes to the debugging of any database.

It helps the developers to fetch or retrieve values faster from the database. Also, indexes take up a smaller space as compared to the real database, as it doesn’t hold every information about a certain row, just it’s address and ID which can uniquely identify that row / address.

Use of INDEX also encourages efficient searching of values or items from the database, improves the JOIN performance, optimizes ORDER BY and GROUP BY clauses and also helps in acceleration Aggregate Functions on those columns on which an Index has been created.