Index (INDEX) in SQL

SQL index (INDEX) is a special lookup table that database search engines can use to quickly increase the time and performance of data retrieval.

Simply put, an index is a pointer to each value that appears in the indexed table / column. Index in Database has the same meaning as the entries in the Index of a book.

INDEX speeds up SELECT queries containing WHERE or ORDER clauses, but it slows down data entry with UPDATE and INSERT statements. Indexes can be created or deleted without affecting data.

Index types available in SQL:

1. Single-Column Index
2. Unique Index
3. Composite Index
4. Implicit Index

To create an index we use the CREATE INDEX command , you can name the index, specify the table, the columns you want to index, and specify the index in ascending or descending order.

CREATE INDEX command

The basic syntax of CREATE INDEX command in SQL is as follows:

 CREATE INDEX ten_index ON ten_bang; 

SINGLE-COLUMN index

Single-Column Index is created for only 1 column in the table. The basic syntax is as follows:

 CREATE INDEX ten_index 
 ON ten_bang (ten_cot); 

UNIQUE index

Unique Index is a unique index, used to increase performance and ensure data integrity. A unique index does not allow any duplicate values ​​to be inserted into the table. Basic syntax is as follows.

 CREATE UNIQUE INDEX ten_index 
 ON ten_bang (ten_cot); 

Index COMPOSITE

Composite Index is a combined index for two or more columns in a table. Its basic syntax is as follows:

 CREATE INDEX ten_index 
 ON ten_bang (cot1, cot2); 

Note :

1. Creating Single-Column Index or Composite Index depends on how often you use the WHERE clause of the query as a filter condition.
2. If only one column is used, the best option is the Single-column Index . If two or more columns are used frequently in the WHERE clause as filters, then the Composite Index index is the more optimal option.

IMPLICIT INDEX

Implicit Index is an index that is created automatically by the Database Server when a table is created. Default indexes are automatically created for Primary key constraints and Unique constraints.

DROP INDEX command

When you don't need to use INDEX, you can DROP according to the following syntax:

 DROP INDEX ten_index; 

You should be careful while deleting an index, because then the performance may be slower or not improved.

When should I avoid using INDEX?

Although INDEX is intended to improve Database performance, sometimes you should avoid using them. Here are some cases you need to consider to decide whether to use INDEX:

1. Should not be used in small tables, few records.
2. Index should not be used in tables where frequent UPDATE and INSERT operations occur.
3. It should not be used for columns that contain a large number of NULL values.
4. Index should not be used for columns that are frequently modified.

In the next article, Quantum will discuss with you how to use the ALTER TABLE command in SQL. Have you remembered to it!

Previous lesson: Create a temporary name using ALIAS in SQL

Next lesson: ALTER TABLE statement in SQL