Here are four brainy ideas to choose columns for creating an index on a Table.
- When the column is foreign key
- When the column is frequently used in search conditions
- When the column contains large number of distinct values
- 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.
|dept_no||Foreign 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.
The column is such that the frequency of updating is rare(Not regular). Such type of column you can use to create index.
Get new content delivered directly to your inbox.