SQLite Indexed By

  Total Views : 408
  Comments : 0
  Keywords : sqlite indexed by to enforce query planner to use specified index with example, sqlite indexed by to enhance query performance with example

Here we will learn what is indexed by in SQLite and how to use SQLite indexed by with examples.

SQLite Indexed By

In SQLite “INDEXED BY” clause is used to force the query planner to use specified index while querying on table.

 

Generally, in SQLite we will defined INDEXED BY in query like “INDEXED BY index-name” to return values in preceding table based on defined index. In case if the specified index-name not exists then SQLite statement will throw error.

 

In SQLite mostly INDEXED BY is used with SELECT, UPDATE and DELETE statements and it is not portable with any another other database engines.

Syntax of SQLite Indexed By

Following is the syntax of using SQLite INDEXED BY with SELECT / UPDATE / DELETE statements.

 

SELECT | UPDATE| DELETE column-list

INDEXED BY (index_name) table_name

WHERE (CONDITION);

Example of SQLite Indexed By

Now we will see how to use SQLite INDEXED BY Clause with example for that create emp_master table and insert some data using following statements.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 500000,3),

(9,'Shwets','Jariwala',19400,2);

Now run the following query to check records of emp_master table.

 

sqlite> SELECT * FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2 

12          Sonal       Menpara     20000       4

Now we will create index (idx_emp_salary) on salary column of emp_master table using following statement.

 

CREATE INDEX idx_emp_salary ON emp_master(salary);

Now we will use INDEXED BY clause on emp_master table to get data using newly created index (idx_emp_salary) like as shown below.

 

SELECT * FROM emp_master INDEXED BY idx_emp_salary WHERE salary > 12000;

If you observe above query we used “INDEXED BY” statement to fetch records from emp_master table based on idx_emp_salary index.

 

Following is the result of emp_master table based on index specified index (idx_emp_salary).

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

6           Sonal       Menpara     13000       1

2           Shweta      Jariwala    19300       2

9           Shwets      Jariwala    19400       2

12          Sonal       Menpara     20000       4

3           Vinay       Jariwala    35000       3

8           Khyati      Shah        49900       3

In case if the defined INDEX name not exists in emp_master table then it will throw error like as shown below.

 

Error: no such index: idx_emp_phne

This is how we can use SQLite indexed By in Select / UPADATE / DELETE statements based on our requirements.

 

PREVIOUS

SQLite Indexes
 

NEXT

SQLite Triggers  
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User