SQLite create index

Introduction to SQLite create index

SQLite provides the create index facility to the user. Index is a special view of any table and database search engine can be used to speed up data access or we can say that to speed up data retrieval. When we talk about relational databases a table consists of a list of rows and each row uses the same column structure that we defined at the time of table creation, every table has a unique id that is rowid (sequence number) is used to identify rows uniquely. With the help of rowid we access data or tables but index has an opposite relationship that means it provides the additional data structures that are useful to increase the performance of SQL statements. SQLite uses B tree data structure for indexing.

Syntax:

create [unique] index specified index name on specified table name (colm list);

Explanation:

• In the above syntax we use the create index statement to create a new index, here the specified index name means actual index name that we need to create. ON and INDEX is keyword and specified table means exited table with column name.
• Here unique is an optional part of this syntax if we have more than one column and that value we need to define uniquely so that we can use a unique keyword.

How to create index in SQLite?

Now let’s see how creating indexes works in SQLite as follows.

• Each index should be related with a particular table.
• An index comprises at least one segment, however all segments of a list should be in a similar table.
• A table may have different indexes.
• At whatever point you make an index, SQLite makes a B-tree design to hold the file information.
• The index contains information from the segments that you indicate in the index and the related rowid esteem.
• This assists SQLite with finding the line dependent on the estimations of the listed sections.
• Envision an index in the information base like a record of a book.
• By taking a gander at the record, you can rapidly distinguish page numbers dependent on the catchphrases.

Example of SQLite create index

Given below is the example mentioned:

First create a new table by using the following statement as follows.

Code:

create table emp (emp_first_name text not null, emp_last_name text not null, email text not null);
.table

Explanation:

• In the above example we use a create table statement to create a new table name as emp with different attributes such as emp_first_name, emp_last_name and email with text data type and not null constraint as shown in above statement.
• The end out of the above statement we illustrated by using the following screenshot.

Output:

Suppose we need to create an email column unique from the emp table. At that time we can use unique keywords as follows.

Code:

create unique index indx_email on emp (email);

Explanation:

• In the above statement we use the create index statement to create a new index name as indx_email on emp table as shown in above statement.

Now we successfully created an index on the emp table and now perform insert operation by using insert into statement as follows.

Code:

insert into emp (emp_first_name, emp_last_name, email) values (“Sameer”, “Varma”, “sameerv@gmail.com”);

Explanation:

• By using the above statement we successfully inserted one record.
• The end out of the above statement we illustrated by using the following screenshot.

Output:

Now try to insert duplicate email by using the below statement as follows.

Code:

insert into emp (emp_first_name, emp_last_name, email) values (“Jay”, “Sharma”, “sameerv@gmail.com”);

Explanation:

• In the above statement we use insert into statement and we try to insert duplicate email, but it shows the error message because of the unique constraint.
• The end out of the above statement we illustrated by using the following screenshot.

Output:

Now insert one more record into the emp table as follows.

Code:

insert into emp (emp_first_name, emp_last_name, email) values (“Jay”, “Sharma”, “jays@gmail.com”);
select * from emp;

Explanation:

• In the above example we use insert into statement to insert new records into the emp table.
• The end out of the above statement we illustrated by using the following screenshot.

Output:

Suppose we need to find out particular details that are based on the specific email so that we can use the following statement as follows.

Code:

select emp_first_name, emp_last_name, email from emp where email = “jays@gmail.com”;

Explanation:

• In the above example we use a select statement to see details from the emp table for the specified email by using where clause as follows.
• The end out of the above statement we illustrated by using the following screenshot.

Output:

Now to check how SQLite database uses indexing by using the following statement as follows.

Code:

explain query plan select emp_first_name, emp_last_name, email from emp where email = “jays@gmail.com”;

Explanation:

• In the above example we use the explain query plan command to see how index is working.
• The end out of the above statement we illustrated by using the following screenshot.

Output:

Now see how we can create a multi column index in SQLite as follows.

Sometimes we need to create a multi column index to sort the data.

At that time we can use the following statement as follows.

We have an already created table that emp.

Code:

create index indx_mul on emp (emp_first_name, emp_last_name);

Explanation:

• By using the above statement we successfully created a multi column index.
• The end out of the above statement we illustrated by using the following screenshot.

Output:

Now filter records as per our requirement, suppose we need to filter records by the emp_first_name column as that we can use the following statement as follows.

Code:

select emp_first_name, emp_last_name, email from emp where emp_first_name = “jay”;

Similarly we can filter records by using both emp_first_name and emp_last_name column by using the following statement as follows.

Code:

select emp_first_name, emp_last_name, email from emp where emp_first_name = “jay” and emp_last_name = “Sharma”;

But SQLite does not use a multicolumn index.

Suppose we need to see the created index list at that time we can use the following statement as follows.

Code:

PRAGMA index_list (‘emp’);

Conclusion

From the above article we see different examples of SQLite create indexes. We also saw the rules of SQLite create index. From this article we saw how and when we use SQLite to create indexes.

Recommended Articles

This is a guide to SQLite create index. Here we discuss the introduction, how to create index in SQLite? and example respectively. You may also have a look at the following articles to learn more –

1. SQL DDL Commands
2. SQL Clustered Index
3. SQL Super Key
4. SQL LOCK TABLE