Advantages and disadvantages of indexes

First, why do you want to create an index (advantage)?
This is because creating an index can greatly improve the performance of the system.
First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.
Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes.
Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data.
Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.
By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Ii. disadvantages of establishing a directional index (disadvantage)
Perhaps someone will ask: there are so many advantages to adding indexes, why not create an index for each column in the table? Although this kind of thought has its rationality, but also has its one-sidedness. Although indexes have many advantages, it is very unwise to add indexes to each column in a table. This is because there is a lot of downside to increasing the index.

First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.
Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger.
Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

Three, guidelines for creating directional indexes
The index is based on some columns in the database table. Therefore, when you create an index, you should carefully consider which columns you can create an index on, and on which columns you cannot create an index.
In general, you should create indexes on these columns.
The first,   can speed up the search on columns that are frequently searched;
The second,   on the column that is the primary key, forcing the column to be unique and arranging the structure of the data in the organization table;
Third,   These columns are often used on connected columns, which are mainly foreign keys that can speed up the connection;
Fourth,   creates an index on a column that often needs to be searched by scope, because the index is sorted and its specified range is contiguous;
Fifth,    Create indexes on columns that often need to be sorted, because the indexes are sorted so that the query can take advantage of the sorting of the indexes to speed up the sort query time;
Sixth,   The use of the columns in the WHERE clause to create an index, speeding up the judging speed of the condition.

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics:
First, the index should not be created for columns that are seldom used or referenced in queries. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.
Second, you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval.
Third, for those columns defined as text, the image and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or has very little value.
The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting. When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.

Advantages and disadvantages of indexes