MySQL: What points should we consider when create composite index
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.