MySQL: What points should we consider when create composite index There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key. 
Beside this, we create composite index on the basis of our SQL query requirements. Ordering of column in a composite index plays a vital role in the selection of right index in query execution which improves the performance.
If a composite index has only 2 columns then its easy to determine their order. 


But, how we will decide the ordering of columns if we have many
columns and many different SQL queries.
What are the other points which should be focused when create composite index?
What is the role of cardinality and modality of a column in creating index and composite indices?
But, how we will decide the ordering of columns if we have many columns and many different SQL queries.


More selective columns should go first.  


  What are the other points which should be focused when create composite index?


Not necessary all fields on an index may be used. For example, if you have index (a, b, c) and WHERE clause looks like 

WHERE a = 5 and b &lt; 10 and c = 6


then only a and b fields may be used. A field with &lt; or &gt; will be the last one that's used.


  What is the role of cardinality and modality of a column in creating index and composite indices?


Cardinality of a set is number of element in the set. The higher cardinality of a field values set the closer the fields should come in the composite index.