0

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?

1 Answer 1

1

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 < 10 and c = 6

then only a and b fields may be used. A field with < or > 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.

1
  • Given the WHERE clause in your answer,, the index should really be (a,c,b). That should yield a smaller index scan. Feb 18, 2014 at 2:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.