General Note:

Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database

INDEX:

The main purpose of an index is to speed up the data retrieval. Assume we have a book, which had 10,000 pages. I want to read particular topic .So i will search the topic in the 10,000 pages. Its hectic process. That's why, we have index in book to easily find out the particular keyword or topic. Likewise ,a table can have millions of records. If we looking for particular records from that million records,it will cause more performance problem.

To avoid this, we using index.

Syntax:


CREATE [UNIQUE] INDEX ON table_name(column_name1,[column_name2,......])

we can create index in mobile sales table. It has more columns around 67 rows.(Actually there is no need to create index here . only for example purpose)


create index mobile_sales_index on mobile_sales_details(brand_name)

UNIQUE is option. If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed.

Any attempt to insert a duplicate entry will result in an error. For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique.

When to Create Index:

1. A table had large volume of records

2. A column ,which is frequently used in where condition

3. A column has more null values

4. The query returns a very small amount of rows from the large volume of records

DROP INDEX:

we can drop index by using the DROP keyword syntax:


DROP INDEX index_name;

PARTIAL INDEX:

If we using index, it will affect all the rows in that column. but if you need to apply index only the subset of table, we have partial index concept. There is no syntax change in partial index. But we have to add where condition after the column list in index .

Syntax:


CREATE [UNIQUE] INDEX ON table_name(column_name1,[column_name2,......]) WHERE condition

create index mobile_sales_index1 on mobile_sales_details(brand_name) where brand_name='Samsung'

The above query will create on mobile sales table but the index will be applied to table when you met the where condition


select * from mobile_sales_details where brand_name='Samsung'

UNIQUE PARTIAL INDEX:

A partial index definition may include the UNIQUE keyword. If it does, then SQLite requires every entry in the index to be unique. This provides a mechanism for enforcing uniqueness across some subset of the rows in a table. For example, suppose you have a database of the members of a large organization where each person is assigned to a particular "team". Each team has a "leader" who is also a member of that team.

The table might look something like this:


CREATE TABLE person( person_id INTEGER PRIMARY KEY,
 team_id INTEGER REFERENCES team,
 is_team_leader BOOLEAN, -- other fields elided );

The team_id field cannot be unique because there usually multiple people on the same team. One cannot make the combination of team_id and is_team_leader unique since there are usually multiple non-leaders on each team. The solution to enforcing one leader per team is to create a unique index on team_id but restricted to those entries for which is_team_leader is true:


CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;

Coincidentally, that same index is useful for locating the team leader of a particular team:


SELECT person_id FROM person WHERE is_team_leader AND team_id=1;