>Are you saying to do a count for each condition of the filter for one field. Based on those conditions build the SQL so that the filter which returns the smallest number of records comes first in the where clause and so on so that the one that gets the highest number is last?

I don't think this will work correctly. The COUNT itself will be slow if a lot of records match a certain part of a condition. You should rather "guess" beforehand which conditions should be included in Rushmore Optimization, and which should be bypassed.

Examples:

InvoiceType = "D" (where InvoiceType can only have a few values, therefore, lots of records for each possible value): delete index from the start. Its use will usually be counterproductive.


InvoiceDate = ldDate: Probably only a few records will match this part. Keep the index.


InvoiceDate > ldFromDate: Probably a lot of records will match this part. Try to bypass RO (Rushmore Optimization) for this part, changing the condition to something like InvoiceDate + 0 > ldFromDate

In general, bypass RO for any part of a condition that uses >, <, # and <>, >=, <=, between(). Don't bypass it if the condition is used by itself. Don't bypass it for =, ==, inlist(). Bypass for "=", if it is part of a larger condition, compares a string, only matches a single character of a larger string, and SET EXACT OFF.


Obviously, you will have to do some trade-offs to keep your algorithm simple. The main idea, I understand, is to have MOST queries within a reasonable time.

Hilmar.