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..

dept_noForeign 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.

Technics to choose index column

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

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.