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?
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.