Set an Index when searching for characters

[maxmangion]

Hi

I have a field which I use it a lot to search for a series of characters within its text. Am I right in saying that by adding an index on this field it wouldn't produce faster results? My reasoning is that with this type of query there is no means of establishing where the index tree should start/stop searching, so it still has to go through all the records?

Thank you in advance for your feedback.

 

[spikepl]

Your reasoning seems right to me, so long as JET/ACE is involved. I have little experience with MS SQL, but I've seen an application once whic used some native facilities there for indexing text.

 

[Galaxiom]

You could generate an index table. This would have a field for the character and another for the PK of the record where that character is found. There will be one record in the index table for each record in the main table where that character occurs.

It is not particularly useful indexing on one character that is likely to appear in most records but it certainly works well when looking for short strings or words.

Of course the generation of the index can take considerable time but thereafter the search is much faster.

 

[maxmangion]

Thank you very much both for your replies. I will try to look into the index table as suggested.

Once again thank you.