An index (INDEX) in SQL is a special lookup table that database search engines can use to speed up data retrieval time and performance.
Simply put, an index is a pointer to each value that appears in the indexed table/column. Indexes in the Database have the same meaning as the entries that appear in the Table of Contents 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 the data.
Types of indexes available in SQL:
- Single-Column Index
- Unique Index
- Composite Index
- Implicit Index To create an index using the CREATE INDEX command, you can name the index, specify the table and columns you want to index, and specify whether the index is in ascending or descending order.
CREATE INDEX command
The basic syntax of the CREATE INDEX command in SQL is as follows:
1 2 | <span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> ten_index <span class="token keyword">ON</span> ten_bang <span class="token punctuation">;</span> |
SINGLE-COLUMN . Index
Single-Column Index is created for only 1 column in the table. The basic syntax is as follows:
1 2 3 | <span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> ten_index <span class="token keyword">ON</span> ten_bang <span class="token punctuation">(</span> ten_cot <span class="token punctuation">)</span> <span class="token punctuation">;</span> |
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. The basic syntax is as follows.
1 2 3 | <span class="token keyword">CREATE</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> ten_index <span class="token keyword">ON</span> ten_bang <span class="token punctuation">(</span> ten_cot <span class="token punctuation">)</span> <span class="token punctuation">;</span> |
COMPOSITE Index
Composite Index is a composite index for two or more columns in a table. Its basic syntax is as follows:
1 2 3 | <span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> ten_index <span class="token keyword">ON</span> ten_bang <span class="token punctuation">(</span> cot1 <span class="token punctuation">,</span> cot2 <span class="token punctuation">)</span> <span class="token punctuation">;</span> |
Note:
- Whether you create a Single-Column Index or a Composite Index depends on how often you use the WHERE clause of your query as a filter condition.
- If only one column is used, then the best choice is Single-columnIndex. If there are two or more columns that are used frequently in the WHERE clause as filters, the Composite Index type of index is the more optimal choice.
IMPLICIT INDEX
Implicit Index (implicit Index) is an index that is created automatically by the Database Server when a table is created. Implicit indexes are automatically generated for Primary key and Unique constraints.
DROP INDEX command
When you don’t need to use INDEX anymore, you can DROP with the following syntax:
DROP INDEX ten_index;
You should be careful while deleting an index, because then performance may be slower or not improved.
When should INDEX be avoided?
Although using 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 or not:
- Not recommended for use in small tables with few records.
- Index should not be used in tables where UPDATE, INSERT operations occur frequently with large frequency.
- Not recommended for columns that contain a large number of NULL values.
- Index should not be used for columns that are frequently modified.
#Source: ST