Hi,
The table has ~2 million records and has 3 columns one of which is a text
field which on an average has 2000 characters(English alphabets, space are
there for now, but if required can be eliminated).
eg: "adfastsdfasgadfdfdsaffagdasfd adfsadfadsgdsfdfsdfsgsdfdsg fg adfafg
adfddfgadsfdsgfghfghjgafedsf............."
The query I am trying is like this...
Select pid,locate('affagdasfd adfsadf',txtfield) from tablename where
locate('affagdasfd adfsadf',txtfield) > 10;
The substring I am searching for is always 30-50 characters in length. The
query execution takes more than 30 seconds and I would like to improve this.
I tried fulltext index on this field hoping that locate() will be faster,
but it dint make any difference. It would be great help if you could help
solve this problem.
thanks
On 6/24/06, John Hicks <[EMAIL PROTECTED]> wrote:
kevin vicky wrote:
> 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?
What is the problem you are trying to solve?
What problem do you have when you use locate()?
Are you running locate on all 2 million records?
> Also since the search string will be between 30-50 characters is there
> any parameters to make the index effective?
I believe locate operates on the text field argument. So it doesn't use
an index.
It sounds like you are confused (or one of us is confused :) so why
don't you just back up and tell us what you are trying to accomplish.
