Hi, I am trying to use locate() function to get position of a substring in a text field which on average has 2000 characters and the search substring is 30 -50 characters long. The table has around 2 million records and looking for a efficient way to do the search. I tried fulltext index but dint see any difference between using it or not. The text field is random characters with space or sometimes no space, does this make the fulltext index not much use? I also wonder about character set and collation, the text field will contain only english alphabets and no special characters so is there a special character set to use rather than the default? Also since the search string will be between 30-50 characters is there any parameters to make the index effective?
If my questions are not clear please let me know I will try to explain better, thanks, Kevin
