Advantages and disadvantages of indexes: when to use or not to use Indexes

Index

2. What is an index?

   An index is a structure that sorts values of one or more columns in a database table. You can use an index to quickly access specific information in a database table.

4. What is the role of an index?

   Indexing is equivalent to a directory on a book. You can quickly find the desired content based on the page number on the directory to Improve the Performance (query speed)

6. Advantages:

   By creating a unique index, You can ensure the uniqueness of each row of data in the database table.

   Accelerate Data Retrieval

   It can accelerate the connection between tables.

   When you use grouping and sorting for search, you can reduce the time for grouping and sorting in the query.

8. Disadvantages

   It takes time to create and maintain indexes. This time increases with the increase of data volume.

   The index occupies physical space. The larger the data volume, the larger the occupied space.

   It will reduce the efficiency of adding, deleting, and modifying tables, because every time you add, delete, and modify indexes, dynamic maintenance is required.

10. When to create an index

    • The primary key automatically creates a unique index.

    • Index should be created for fields frequently used as query Conditions

    • Creating an index for the fields sorted in the query will greatly increase the sorting speed (the index is sorting and fast searching)

    • Fields of statistics or groups in the query;

12. When do I not need to create an index?

    • Frequently updated fields are not suitable for index creation, because each update is not only an update record, but also an Index Update and an index file is saved.

    • Fields not used in the where condition, and no index is created;

    • There are too few table records and no indexes need to be created;

    • Tables that are frequently added, deleted, and modified;

    • Duplicate and evenly distributed fields. Therefore, an index is created for fields that are frequently queried and sorted. Note that some data contains a large amount of repeated data, so the index creation does not have much effect, such as gender fields, only men and women, and is not suitable for indexing.

Advantages and disadvantages of indexes: when to use or not to use Indexes