How to decide Which Table Columns Best for Index

Here are four brainy ideas to choose columns for creating an index on a Table.

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

Here are the Four Ideas with Details

Column is foreign key

In a Table, if any column is foreign key (a key of dependent table), then you must create index on that column.

Employee Table

NameTypes
dept_noForeign key of Dept table

So, you must create index for dept_no.

Being used frequently in search

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

Large number of distinct values

Distinct means, not the same. Unique values. For example, user_id. You can use user_id column to create index.

Updated infrequently

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

Related Posts

Get new content delivered directly to your inbox.

Author: Srini

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