I just found one of my Jr. DBA to create many indexes with lots of column in it. After talking with him I found out that he really does not understand how really Index works. He was under impression that if he has more columns in one index, that index has higher chance of getting selected during execution of query and speed up the query. It was very much incorrect. He did not understand important of the order of column in created index.
Order really matters and the column which is at first order matters the most in Index. The selection of index is entirely based on first column. An index is considered for use only if the first column listed in the index is used in the query. So if there is no match on first column and column used in JOIN, ORDER BY, or WHERE clauses of the query, index is completely ignored. It also does not speed up index performance if many columns are used while creating index. It may slow down the usage of the table instead.
Ideally, create small index on most important columns used in JOIN, ORDER BY, or WHERE clauses of the query.
Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
