> -----Ursprüngliche Nachricht-----
> Von: Ann W. Harrison [mailto:aharrison@...]
> Gesendet: Mittwoch, 1. Dezember 2004 01:36
> An: firebird-support@yahoogroups.com; firebird-support@yahoogroups.com
> Betreff: Re: [firebird-support] Re: Can someone explain to me
> what's the best way to do this?
>
>
> The answer, for your application, may be to find a text
> indexing package for Firebird - there are some, though I
> don't have a pointer at the moment.
>

The most simple solution is to use a stored procedure for tokenization (or an UDF) and some triggers. However, when only searching for "names CONTAINING 'i'" this wont yield much performance gain. How fast such an indexed search can be, depends mostly on the data distribution, which isnt perfect when indexing a table full of names. A simple scenario for a fulltext index contains a table with a lowercased word, and a foreign key to the row in which the word occurs. When there are many repeating words, a mapping table for n:m relation between token-table and data-table should be included. Triggers update these tables when data is inserted, modified and deleted. When searching for full words/tokens, the token-table can be queried via an index, which can be quite fast even when scanning several thousand records for the specified word:

SELECT * FROM Index_Names INNER JOIN Names ON Index_Names.FID = Names.ID WHERE Index_Names.Name='albert';

This would yield for example these rows from Names:

"Albert"
"Steven Albert"
"Sven Albert Smith"

In your case, maybe the most simple option is to enlarge the server ram and firebirds page cache, so that there wont be too many disk I/Os necessary to lookup the record you are searching for. Every disk I/O costs at least the time to position the head to the sector on the drive, which is much slower than simply reading data from RAM.

Regards, Alex