Learn about indexes in SQL

Tram Ho

1. What is Index?

An index (or index) is a data structure to increase the database’s query performance. Index allows the database to execute some conditional queries faster than usual. But the index is also stored in memory and consumes memory space and time to create and update the index, so it is necessary to think carefully when using the index.

2. When to use index?

Index helps speed up the querying of some conditional select statements so it is especially useful when the query statement is used frequently (or the indexed column is often queried) and the number of records is large. .

It is difficult to determine when to use an index, it depends a lot on practical problems but there are some common rules when choosing a column (or set of columns) to create an index:

  1. Keys and columns have unique values: Database will automatically create indexes for these columns, so to avoid duplication and consume memory, we should not create index for them.
  2. Frequency used: When the query frequency is greater, creating index will help reduce query time (sum) as much.
  3. Number of records in a table: The greater the number of records in the table, the faster the query speed will be, the more obvious the advantage of using an index on these tables is over those with a small number of records. Especially for the case of a table with few records (100 – several thousand) we should not index them.
  4. Rapid growth table data: The index will automatically update when a record is added to the database, so when indexing a table it will slow down the addition and deletion of the record. So a table that is frequently updated should have less index than a table that is rarely updated.
  5. Memory space: When creating an index, it will use the memory space of the database, so when the database is large, we need to carefully choose which field to use as the index.
  6. Data has a variety of values: The index is created based on the values ​​in the column that it points to, for example, the index column is created with only 3 values ​​A, B, C, the index created will have a much smaller value. compared to a column with an alphabetical range of values. Indexing on a column with few values, for example, the sex column will not speed up the query much, but for columns with many distinct values, such as the name column, the query will speed up significantly.

3. Advantages of using indexes.

  1. Usually will increase query performance when conditions fall on indexed columns.
  2. Help us to query data faster.
  3. Can be used to organize data.
  4. Unique indexes ensure uniqueness of fields in a database.

4. Disadvantages of using indexes.

  1. Decreased performance of insert, update, and delete statements.
  2. Use up memory.

5. Create indexes in PostgreSQL.

To create an index in PostgreSQL we use the following command:

CREATE INDEX ten_index ON [TABLE NAME] (COLUMN1, COLUMN2, ...)

If you want to create a unique index (no duplicate data is allowed in the table):

CREATE UNIQUE INDEX ten_index ON [TABLE NAME] (COLUMN1, COLUMN2, …)

When you do not need to use the index anymore, you can delete it with the syntax:

DROP INDEX ten_index

There are also many options when creating index, for more information, please refer to the link: https://www.postgresql.org/docs/11/sql-createindex.html

6. References.

Share the news now

Source : Viblo