I have a documents database where I use a hierarchical tagging system. A search field is populated from all of the accumulated tags to make searching easy. Since the search field is the result of a calculation, it can't be indexed resulting in slow searches. Can anyone help this newbie figure out how to get around that so I can speed up the search?
Much depends on how that calculation works to build that list of tags. Not all calculations have to be unstored. I used to have a stored calculation field that built a list of keywords from a description field in the same record, for example.
And if a user selects what tags are to be used for a given record tag, the act of selecting the tag can update an indexed list of tags (or tag IDs) in a field.
So feel free to tells how tags are determined for a given record in your solution.
The usual solution is to make the process of adding and removing tags fully scripted so that it updates the search field on each modification and sets the results statically. That will give you the best result IMHO.
An easy way out is to create a secondary field and have a server-side schedule update that second field with the result of the calc field every hour or once a day. Then base your search on that secondary static field. Downside is that you have that second field and the duplicate data that goes with it, and the fact it will not always be fully in sync with the actual data.