Latest web development tutorials

SQLite index

Index (Index) is a special lookup table, a database search engine used to speed up data retrieval. Simply put, the index is a pointer to the data in the table. A database index of a book behind the index is very similar.

For example, if you want to discuss a particular topic in a book all the pages referenced, you first need to point to an index, an alphabetical listing of all topics, and then point to one or more specific page number.

Indexes allow quick SELECT query and the WHERE clause, but it will slow down data entry using UPDATE and INSERT statements when. Index can be created or deleted, but will not affect the data.

Create an index using the CREATE INDEX statement, which allows you to name the index, specify the table to be indexed and one or more columns, and indicates the index is ascending or descending order.

Index may be unique, and similar UNIQUE constraint to prevent duplicate entries in the column or combination of columns.

CREATE INDEX command

CREATE INDEX The basic syntax is as follows:

CREATE INDEX index_name ON table_name;

A separate index

The index is based on only a single index on a list created. The basic syntax is as follows: 

CREATE INDEX index_name
ON table_name (column_name);

Unique Index

Using a unique index is not only for performance, but also to the integrity of the data. Unique index does not allow any duplicate values ​​into the table. The basic syntax is as follows: 

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Composite index

Composition index is based on two or more columns of a table created. The basic syntax is as follows: 

CREATE INDEX index_name
on table_name (column1, column2);

Do you want to create a separate index is a composite index, taking into account the very frequent as your query filter conditions in the WHERE clause columns.

If the value of the use of a column, choose to use a separate index. If there is a filter in the WHERE clause often use two or more columns, then choose to use a combination of the index.

Implicit index

Implicit index is that when you create an object, created automatically by the database server. Indexes are automatically created as a primary key constraints and unique constraints.

Examples

Here is an example, we will create an index on the salary column COMPANY table: 

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Now, let's use.indices command lists all available indexes on the COMPANY table as follows:

sqlite> .indices COMPANY

This produces the following results, whichsqlite_autoindex_COMPANY_1implicit index is created when you create a table. 

salary_index
sqlite_autoindex_COMPANY_1

You can list all the index database ranges, as follows: 

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

DROP INDEX command

An index can use the SQLiteDROP command to delete.Special attention should be when to delete the index, because the performance may decrease or increase.

The basic syntax is as follows: 

DROP INDEX index_name;

You can use the following statement to delete an index created earlier: 

sqlite> DROP INDEX salary_index;

Under what circumstances to avoid the use of the index?

Although the index is to improve database performance, but here are a few cases the need to avoid the use of the index. When you use an index, the following guidelines should be reconsidered:

  • Index should not be used on small tables.

  • Index should not be used in large quantities there are frequent updates or table insert operation.

  • Index should not be used in the column contains a large number of NULL values.

  • Index should not be used on a frequent operation of the column.