We should create indexes on the columns which are,
- Mostly used in WHERE clause
- Mostly used in JOIN conditions
- Mostly used in Order by
If we are creating a concatenated index on more than one column, give first preference to the column which is mostly used in the query.
If we are not sure on which column to be created an index, check the execution plan which gives us the idea on the query how it is executing.
We should not create indexes,
- On all columns if we do not know on which column to create. It will definitely reduce the query performance. Because, when we are Inserting/Deleting/Updating the data, it has to update rows in index.
- On small tables which has less number of records. It may work faster than the query with indexes.