How to Opt Which Columns to be Indexed in SQL

Here are four technics to opt a column for an index in SQL.

Technics to choose index column

1. if the column is foreign key
2. If the column is frequently used in search conditions
3. If the column contains large number of distinct values
4. If the column is updated infrequently

Technic#1: Column is foreign key

In a Table, if any column is a foreign key (a key of a dependent table), you can create an index on that column. So, you can create an index for dept_no..

Name	Types
dept_no	Foreign key of Dept table

Technic#2: Being used frequently in search

If you want to search a table based on a specific field /or column, then you can create an index on that column. For instance, the columns you use in the WHERE clause of SQL.

Technic#3: Large number of distinct values

The distinct values are Unique values. The user_ids are unique. So you can use the user_id column to create an index.

Technic#4: Updated infrequently

The column is such that the frequency of updating is rare(Not regular). Such type of column you can use to create an index.

Related Posts