Here we will learn what are the indexes in SQLite and how to create indexes on single or multiple columns in SQLite and delete or drop SQLite indexes with example.
Generally in SQLite indexes are used to improve the performance of data retrieval process by quickly identify the records in table instead of searching each and every row of the database table.
General example for SQLite indexes is book index suppose we have one travel guide book and we want to know about particular city information but we don’t know on which page that city information available that time what we will do? We will search for all the pages in the book and automatically huge time get wasted for searching particular city information.
Instead of that if we refer the index page of book immediately we will know that in which page that city information exists because in index all the topics listed in particular order so we can directly go to that page instead of wasting time to search each page of the book same way indexes in SQLite will help us to improve the performance of data retrieve process.
In SQLite whenever we create index on table column it will rearrange the table records and it will use extra storage space to maintain the index data structure.
Generally, in SQLite when we create a table (Person) without Index that will be like as shown below.
| Row_id | First_name | |
|---|---|---|
| 1 | Shweta | shweta@gmail.com |
| 2 | vinay | vk@gmail.com |
| 3 | sonal | Sud@gmail.com |
| 4 | yamini | sonu@yahoo.com |
| 5 | jagruti | jagu@gmail.com |
When we create an index on the table (Person) column Email that will create additional table data structure by rearranging the records with RowId like as shown below to increase the query performance.
| Row_id | |
|---|---|
| shweta@gmail.com | 1 |
| vk@gmail.com | 2 |
| Sud@gmail.com | 3 |
| sonu@yahoo.com | 4 |
| jagu@gmail.com | 5 |
In SQLite, indexes will help us to return data quickly in SELECT operations by reducing the time required to scan table rows but it will slow down the process while performing UPDATE or DELETE operations.
Generally, in SQLite to create index we use CREATE INDEX command. Now we will see how to create indexes on table columns with examples.
Following is the syntax of creating indexes on table columns in SQLite.
CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name ON table_name(column_names);
If you observe above SQLite CREATE INDEX statement we defined multiple parameters those are
UNIQUE – We use this parameter to make sure column values must be unique like phone no, etc. and this one is an Optional.
IF NOT EXISTS – It will prevent throwing errors in case if we try to create index names that already exist.
index_name – Its name of the index which we are trying create and it must be unique.
table_name – Its name of the table which we used to create the index.
column_names – Name of a table column in which we want to create an index. It will be like column1, column2… column_n.
We will see how to create an index on table columns in SQLite with examples. For that first create table called Person using the following query.
CREATE TABLE Person
(SSID INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT,
phone_no TEXT,
city TEXT);
Now we will create an index on the phone_no column of Person table using the following query.
CREATE INDEX idx_person_phone ON Person(phone_no);
The above query will create an index on the Person table phone_no column.
By using the EXPLAIN QUERY PLAN statement we can easily check if SQLite table using an index or not while fetching data. Following is the statement to check indexes in a table.
EXPLAIN QUERY PLAN
SELECT first_name,last_name,email
FROM Person
WHERE phone_no = '84697';
selectid order from detail
---------- ---------- ---------- ----------------------------------------------------
0 0 0 SEARCH TABLE Person USING INDEX idx_person_phone(phone_no=?)
Now we will create a UNIQUE index on the email column of the Person table using following query.
CREATE UNIQUE INDEX idx_person_email ON Person(email);
If you observe the above query we are creating INDEX on the email column and it will allow only UNIQUE values.
Now we will insert some records in the Person table using following query.
INSERT INTO Person(first_name, last_name, email, phone_no, city)
VALUES('Vinay', 'Jariwala', 'vinay@gmail.com', '8798878', 'Vapi');
Now if we try to insert same email in Person table using following query.
INSERT INTO Person(first_name, last_name, email, phone_no, city)
VALUES('Vinay', 'patel', 'vinay@gmail.com', '965652', 'Surat');
Error: UNIQUE constraint failed: Person.email
Whenever we try to insert the same email in a table then it will throw a unique constraint violation because we created a unique index on the email column.
Now we will see how to create an index on multiple table columns. Generally, in SQLite when we create INDEX on a single column then SQLite uses that column to sort the data. In case if we create an index on multiple columns then SQLite sorts data based on the first column defined while creating an index and then use the second column, third column, etc. to sort the data.
In SQLite the index which we create on multiple columns will call it a composite index.
Following is the syntax to create sqlite index on multiple columns.
CREATE INDEX index_name ON table_name(column1, column2….columnn);
If you observe above syntax we defined multiple columns with a table name to create an index on multiple columns in SQLite.
Following is the SQLite statement to create an index on Person table first_name and last_name columns.
CREATE INDEX idx_person_name ON Person(first_name,last_name);
In the following scenarios whenever we request data from Person table SQLite will use a multi-column index which we created on the table to search for the data.
Only the first_name column in WHERE clause.
SELECT * FROM Person WHERE first_name = 'Vinay';
first_name and second_name columns in WHERE clause.
SELECT * FROM Person WHERE first_name = 'vinay' AND last_name = 'jariwala';
In the following scenarios, SQLite does not use a multicolumn index which we created on Person table.
first_name or last_name column in WHERE clause.
SELECT * FROM Person WHERE first_name = 'vinay' OR last_name = 'jariwala';
last_name column in WHERE condition.
SELECT * FROM Person WHERE last_name = 'jariwala';
By using .schema command we can easily get the structure of a table and the indexes that we created on table.
Following is the query to get the schema & index information of the Person table.
.schema Person
When we run above query we will get result like as shown below
CREATE TABLE person
(ssid INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
email TEXT,
phone_no INTEGER,
city TEXT);
CREATE INDEX idx_person_phone ON Person(phone_no);
CREATE UNIQUE INDEX idx_person_email ON Person(email);
CREATE INDEX idx_person_name ON Person(first_name, last_name);
This is how we can get the table schema and indexes information.
Generally, in SQLite to drop or remove index we use the DROP INDEX command.
Following is the syntax to drop or remove the index in SQLite.
DROP INDEX [IF EXISTS] index_name;
In above syntax IF EXISTS clause is optional and it will help us to prevent throwing error in case if the given index name does not exist in database.
Following is the example to DROP or remove an index from the SQLite database.
DROP INDEX IF EXISTS idx_person_email;
The above statement will check for the index named idx_person_email and if it exists then it will remove from the disk.
In SQLite to rename existing indexes, we don’t have any direct command so first, we need to drop the existing index and then need to create an index with new name on the same column.
Following is the syntax to rename the existing SQLite index.
DROP INDEX [IF EXISTS] index_name;
CREATE [UNIQUE] INDEX [IF NOT EXISTS] new_index_name ON table_name(column_names);
In above syntax index_name is the existing index name and new_index_name is the index name which we used to rename.
Following is the example to rename the existing index in SQLite.
DROP INDEX [IF EXISTS] idx_person_phone;
CREATE INDEX idx_per_phone ON Person(phone_no);
The above example will drop idx_person_phone index and will create new index idx_per_phone in SQLite database.
This is how we can use indexes in SQLite based on our requirements.