EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQLite Tutorial SQLite create index

SQLite create index

Updated April 1, 2023

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

SQLite create index 1

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", "[email protected]");

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:

SQLite create index 2

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", "[email protected]");

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 try to insert duplicate email

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", "[email protected]");
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:

insert one more record

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 = "[email protected]";

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:

SQLite create index 5

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 = "[email protected]";

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:

SQLite create index 6

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:

already created table that emp

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

We hope that this EDUCBA information on “SQLite create index” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL DDL Commands
  2. SQL Clustered Index
  3. SQL Super Key
  4. SQL LOCK TABLE
SPSS Course Bundle - 14 Courses in 1 | 5 Mock Tests
34+ Hours of HD Videos
14 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
HADOOP Course Bundle - 32 Courses in 1 | 4 Mock Tests
125+ Hour of HD Videos
32 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
INFORMATICA Course Bundle - 7 Courses in 1
47+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
SQLite Course Bundle - 4 Courses in 1
 10+ Hours of HD Videos
4 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more