Indexes and string functions conditions

[Sebastian Araya]

Hello !!
I have to tables (name A and B, B has a fulltext index). In need to
handle this type of query:

select A.* from A, B where Instr(A.field1,B.field2) and Match (B.text)
Against ('foo');

because field2 on B has a substring of the A.field1. Please note that
I have also an index on A.field1, and another on B.field2.

The issue is that I'm having too much time consuming process 'cos
Instr() doesn't seem to use the indexes, even using "force index"...
Any work around... any clue... ??

Thanks!

Sebastian.

[Bill Karwin]

Sebastian Araya wrote:

The issue is that I'm having too much time consuming process 'cos
Instr() doesn't seem to use the indexes, even using "force index"...
Any work around... any clue... ??

Yes, this operation cannot benefit from an index.

Think of it this way: if I ask you to look up people in a telephone
book, and I'm looking for last names starting with "Stev", you can find
them pretty quickly because the book is alphabetized. But if I ask for
names where the string "stev" can occur anywhere in the name, you find
it harder to do the search and you have to search the whole book.

Bill K.

[numisys]

Hello Bill K,
thanks for the answer, but it's seems I didn't explain really good.

My problem is this: I'm searching for two conditions:

1. 'foo' in field text of table B, that's works fine using
fulltext index;
2. the leftmost string A.field1 which I like to match with
B.field2 (which I don't know really it's length so I
use Instr() ), but I have tried with:
B.field2 = left( A.field1, length( B.field2 ) )
allways getting the same results: it didn't use the indexes;

A little example may unoffuscate my problem: let A.field1 = 'ABCDEFG'
and B.field2 = 'ABC', and using B.field2 = left( A.field1, length(
B.field2 ) ), I think I don't break any index law, 'cos I'm looking for
starting strings.

So, forgive my uncleary exposure... then again, any clues ??
Sebastián.

[Bill Karwin]

nu*****@gmail.com wrote:

A little example may unoffuscate my problem: let A.field1 = 'ABCDEFG'
and B.field2 = 'ABC', and using B.field2 = left( A.field1, length(
B.field2 ) ), I think I don't break any index law, 'cos I'm looking for
starting strings.

Well, you may be looking for starting strings, but that's not what
INSTR() looks for. That function looks for a substring _anywhere_
inside the larger string, so it can't assume that you mean that the
string must occur at the start.

The other example you describe using LEFT() may not be able to use the
index on A.field1 because you're taking only part of the value that is
indexed. It'd be like indexing an integer column x and then searching
on "x MOD 100" or something.

Try this instead:

select A.* from A, B
where A.field1 LIKE CONCAT(B.field2, '%')
and Match (B.text) Against ('foo');

Bill K.