---
title: "How to Opt Which Columns to be Indexed in SQL"
description: "How to choose index columns for a Table. It is tricky. These are ideas to choose correctly."
url: https://srinimf.com/2021/08/07/how-to-decide-which-table-columns-best-for-index/
date: 2021-08-07
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2021/08/how-to-opt-index-column.jpg?fit=2240%2C1260&ssl=1
categories: ["SQL"]
tags: ["Table Index Columns"]
type: post
lang: en
---

# How to Opt Which Columns to be Indexed in SQL

[Here are](https://srinimf.com/2014/09/15/db2-joins-performance-tuning/) 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 |

*Data*

### Technic#2: Being used frequently in search

If you want [to search](https://srinimf.com/2022/03/12/how-to-find-table-name-for-a-column-search-database%ef%bf%bc/) 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](https://i0.wp.com/srinimf.com/wp-content/uploads/2021/08/chjpdmf0zs9sci9pbwfnzxmvd2vic2l0zs8ymdiylta1l2pvyjcyny0yndqtdi5qcgc.jpg?resize=1300%2C1300&ssl=1)

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

- [DB2 Creating Index: 6 Thumb Rules](https://srinimf.com/2015/02/09/rules-for-creating-index-db2/)DateFebruary 9, 2015
- [Everything You Need to Know [About] Alter Index in DB2](https://srinimf.com/2022/03/19/everything-you-need-to-know-about-alter-index-in-db2/)DateMarch 19, 2022
- [DB2 SQL: How to Create Index (1 of 2)](https://srinimf.com/2015/08/09/db2-different-sql-queries-for-creating-index-1-of-2/)DateAugust 9, 2015

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2021%2F08%2F07%2Fhow-to-decide-which-table-columns-best-for-index%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2021%2F08%2Fhow-to-opt-index-column.jpg%3Ffit%3D1200%252C675%26ssl%3D1&description=How%20to%20Opt%20Which%20Columns%20to%20be%20Indexed%20in%20SQL)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2021/08/07/how-to-decide-which-table-columns-best-for-index/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2021/08/07/how-to-decide-which-table-columns-best-for-index/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2021/08/07/how-to-decide-which-table-columns-best-for-index/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2021/08/07/how-to-decide-which-table-columns-best-for-index/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Opt%20Which%20Columns%20to%20be%20Indexed%20in%20SQL&body=https%3A%2F%2Fsrinimf.com%2F2021%2F08%2F07%2Fhow-to-decide-which-table-columns-best-for-index%2F&share=email)
-

### Like this:

Like Loading…