INDEX vs Filter

[DDP021]

Hi...We have a HUGE report we load daily to CMOD9.5 (96000+ documents daily)....It has over 20 searches defined...Users were having issues timing out (using CONTENT NAVIGATOR) when they were doing a search on one of the particular search fields...In researching, we found this field was defined as a FILTER rather than an INDEX on the App Grp...We did manage to update this on the Application Group to change it to a FILTER..This corrected the timeout issue...But we fear we may run into the same timeout issue if someone else attempts to use another Search Field that is also defined as a FILTER...We are understanding if you have TOO many set to INDEX is can have a negative impact..Is this correct?  With a total of 20 searches, how many is TOO many set as INDEX?..Should we try and determine which fields, out of the 20, are used most often and change only those? 

[Greg Ira]

The more Indexes you add the more space you will consume.  If you're not space constrained I wouldn't concern myself too much with the number of Indexes you have defined as the benefits generally outweigh the negatives.  I wouldn't change all 20 to Index but we usually start with 4 to 6 Indexes and tweak it from there.  We'll start small and work our way up watching resource consumption and response times.  We always poll the users to find out which fields they use most, restrict the use of NOT, and when possible force them to always use at least one indexed field.

[DDP021]

Thanks Greg!...You confirmed what we believed to be the case...We're in the process of polling the users who currently are using this report to determine what searches they are actually using...Guessing there are many of the 20 that aren't even used but for some reason, at the time of setup, someone deemed them important!! ;-)......Again, thanks for the input!

[Justin Derrick]

Never trust users to do what they say they're going to do. 

Enable all query logging, and review the logs to see which fields are used more often.  Also, determine the 'cardinality' of each field in your database, and force users to search with those fields.

I'll make a post about this one of these days...

-JD.