SQLite Indexes

  Total Views : 560
  Comments : 0
  Keywords : create index in sqlite with example, sqlite create index on one or more columns with example, sqlite drop or remove index of table column with example, sqlite index for database performance

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.

SQLite Indexes

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 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 in which page that city information available that time what we will do? We will search for all the pages in 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_idFirst_nameEmail
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 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.

 

EmailRow_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.

SQLite Create Index on Table

Generally, in SQLite to create index we use CREATE INDEX command. Now we will see how to create indexes on table columns with examples.

Syntax to Create Index in SQLite

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 which is already exists.

 

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 – The name of table column which we want to create index. It will be like column1, column2… column_n.

Example of Creating Index in SQLite

We will see how to create index on table columns in SQLite with examples. For that first create table called Person using 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 index on phone_no column of Person table using following query.

 

CREATE INDEX idx_person_phone ON Person(phone_no);

The above query will create index on Person table phone_no column.

SQLite Check Table Indexes

By using EXPLAIN QUERY PLAN statement we can easily check if SQLite table using index or not while fetching data. Following is the statement to check indexes in 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=?)

SQLite Create Unique Index

Now we will create UNIQUE index on email column of Person table using following query.

 

CREATE UNIQUE INDEX idx_person_email ON Person(email);

If you observe above query we are creating INDEX on email column and it will allow only UNIQUE values.

 

Now we will inset some records in Person table using following query.

 

INSERT INTO Person(first_namelast_nameemailphone_nocity)

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_namelast_nameemailphone_nocity)

VALUES('Vinay''patel''vinay@gmail.com''965652''Surat');

 

Error: UNIQUE constraint failed: Person.email

Whenever we try to insert same email in table then it will throw unique constraint violation because we created unique index on email column.

SQLite Create Index on Multiple Columns

Now we will see how to create index on multiple table columns. Generally, in SQLite when we create INDEX on single column then SQLite uses that column to sort the data. In case if we create index on multiple columns then SQLite sorts data based on the first column defined while creating index and then use second column, third column, etc. to sort the data.

 

In SQLite the index which we create on multiple columns will call it as composite index.

Syntax to Create Index on Multiple Columns

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 table name to create index on multiple column in SQLite.

Example to Create Index on Multiple Columns

Following is the SQLite statement to create index on Person table first_name and last_name columns.

 

CREATE INDEX idx_person_name ON Person(first_name,last_name);

In following scenarios whenever we request data from Person table SQLite will use multi column index which we created on table to search for the data.

 

Only 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 following scenarios SQLite does not use muticolumn 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';

SQLite Get Indexes from Table

By using .schema command we can easily get structure of table and the indexes which we created on table.

 

Following is the query to get the schema & index information of 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.

SQLite Drop or Remove Index

Generally, in SQLite to drop or remove index we use DROP INDEX command.

Syntax to Drop SQLite Index

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 an optional and it will help us to prevent throwing error in case if the given index name not exists in database.

Example to Drop SQLite Index

Following is the example to DROP or remove index from 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.

SQLite Rename SQLite Index

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 index with new name on the same column.

Syntax to Rename SQLite Index

Following is the syntax to rename 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.

Example to Rename SQLite Index

Following is the example to rename 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.

 

PREVIOUS

SQLite Views
 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User