Multiple Column Indexing

[PaulDeason]

I want to use multiple column indexing to improve my SELECT queries. I have 5 columns that can be searched on, however, anywhere between one and five of the columns can be searched on depending on the user.

Therefore if the user searches on e.g. column 1 and 3, a normal multiple column indexing will not work.

Does anyone know the best solution to do this? Should I just index each column individually, or try and create every possible index combination? Any advice would be greatly appreciated.

 

[Bastien]

what are the column types? indexing a full text column is not desirable for perfomance, but simple elements like name can be used

Bastien

cat, the other other white meat

 

[abombss]

It also depends on the cardinality of the data. I would only index the most frequently used columns that are the most unique. Adding an index to column with few distinct values does not help.

I would log some queries for a couple days, analyze them, then build your indexes.

abombss

 

[PaulDeason]

The database is a job search so the columns are things like industry, company, location, job type, salary. However, apart from salary, the other columns will be integer keys to other tables that have pre-defined options.

The problem is people search for jobs on completely different criteria, leaving some fields blank. Therefore...this is where my problem about how to index comes in.

Thanks for your responses, and any further help would again be appreciated.