SQL index

SQL index

Index is a special query table that can be used by database search engines to speed up the retrieval of data. S imply put, an index is a pointer to the data in a table. The index of the database is very similar to the index behind the book.

For example, if you want to look up all the pages in a book that are related to a particular topic, you'll first query the index (the index lists all topics in alphabetical order) and then find one or more pages in the index that are related to that topic.

Indexes can speed up SELECT queries and WHERE clauses, but they also slow down the data entry process that contains UPDATE or INSERT statements. The creation and deletion of an index does not affect the data in the table.

Creating an index requires the use of a CREATE INDEX statement that allows you to name the index, specify which tables to create the index and which columns to index, and also specify that the indexes are listed in ascending or descending order.

As with UNIQUE constraints, indexes can be unique. In this case, the index prevents duplicate entries in columns (or combinations of columns, some of which have indexes).

CREATE INDEX command:

The basic syntax of the CREATE INDEX command is as follows:

CREATE INDEX index_name ON table_name;

Single-column index:

A single column index is created from a single field, and its basic syntax is as follows:

CREATE INDEX index_name
ON table_name (column_name);

Unique index:

Unique indexes are used not only to improve query performance, but also to ensure data integrity. U nique indexes do not allow any duplicate values to be inserted into the table. The basic syntax is as follows:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Cluster index:

Clustered indexes are built on two or more columns in a table. The basic syntax is as follows:

CREATE INDEX index_name
on table_name (column1, column2);

Whether you create a single-column index or a clustered index depends on which columns appear most often in the WHERE clause that serves as a filter in each query.

If you only need one column, you should create a single column index. If two or more columns are used in the WHERE clause as a filter, cluster indexing is the best choice.

Implicit index:

Implicit indexes are automatically generated by the database server when some objects are created. For example, for primary and unique constraints, the database server automatically creates an index.

DROP INDEX command:

The index can be deleted with the SQL DROP command. You should be especially careful when deleting indexes, which can result in reduced or improved database performance.

The basic syntax is as follows:

DROP INDEX table_name.index_name;

When should I avoid using indexes?

Although indexes are created to improve the performance of the database, there are some situations in which indexing should be avoided. The following guidelines give when to reconsider whether to use indexes:

• Small data tables should not use indexes;
• Tables that require frequent mass updates or insertions;
• If the column contains large numbers or NULL values, it is not appropriate to create an index;
• Columns that operate frequently should not be indexed.