I'm working with a dynamic query for a semi large table (about 1 M records). The result will be displayed on a web site as a paged result (~50 records per page and the possibility to page forward perhaps something like 10 pages).
Since the query is rather dynamic I made it ... dynamic. It's up to the client to select what filtering and ordering options should apply - there are something like 30 different filtering possibilities and I've made indices to match the most likely options (and will add indices based on actual use).
Now some of the options are likely to change in the future and they are of a boolean character - either they match or they don't. I put those options as whitespace delimited strings into a field, that field is the lone member of a word index.
Now basically all searches are quick except those utilizing this word index. Searching the word index is 100 times slower than a good match on the normal indices. The ordering might be one of the reasons for this (as well as word index being slower than "normal" index).
The database seems to run smoothly - nice high buffer hits etc.
I'm considering removing the field, adding lots of logical fields instead, indexing those and then when the options changes I will have to change the database adding fields and index members instead of just adding more strings to the field.
1) Other options/ideas?
2) Are there any specific ways to increase the performance of word indices?
OE11.4 Linux
One thing is that once Progress decides to use a word index it will not use other indexes to satisfy the query. Just the word index (unless you have an OR).
I also had better luck in the past using a field identifier in the word indexed field so instead of "A B C" it would be "Status@A Region@B Rep@C".
Grab one of the problem where clauses and compile/run it in the editor and look at the XREF and the _UserTableStat/_UserIndexStat values and see exactly what is happening.