>>Wow, did not know that! Any idea what size? This client is not dealing with large tables but I would like to know that and take a look if they are anywhere close to the problem size.
>
>Twelve years ago I had to have a search in a series of long texts, and what search was available was slow. Since the whole database with the texts (legal ones, that is) was distributed with the app, it made sense to index everything in advance. So I built a manual index - I had a table of words and word occurrences. The words table had only a pk, the word itself and the word length; the occurrences table had a fk to the word and fk to the text, the position of the word in the text and some numeric score based on text's length, word's length and its position. These were updated whenever a text was saved, or in an initial batch run, when I'd fill those two tables from scratch.
>
>The search would then find the PKs of the words sought, then would pull the PKs of the texts based on these words' occurrences, and sort the results according to accumulated score. It was blazingly fast, even on simple dbfs. I was surprised myself, didn't expect that Rushmore could achieve that speed.

I did something similar. For an application where I needed to search inside a few text fields, I made a function which was called every time the table was changed in any way. The function updated a second table with two fields, one contained a record for every word with more than two characters, The other held a pk back into the main table. I was also pleasantly surprised about the speed.