Composit Index

[krishnaindia2007]

I defined a composit index on three columns
division code
section code
village code
But in where claue i am using only division code and village code.while executing the query it is not using index . Is it compulsory to use all columns or cosecutive columns in where clause to use index?

[debasisdas]

If you ae not using all the fields in query then why you created the composit index un-necessarily. You must refer to all the indexed columns to use the index.

[krishnaindia2007]

I defined composit index on transaction file. The columns I am using in where clause depends on the tables that I am using in join codition.
For exampple in Master table
Divisionmaster I have only divisioncode
Sectionmaster divisioncode, sectioncode
villagemaster divisioncode,sectioncode,villagecode
In some other tables I have only divisioncode and villagecode.
But in most of the cases I am using all the three columns in where clause.
Maximum How may composit indexes may I define on a table?

[debasisdas]

Please find a related discussion here.