>>My tests showed, much to my surprise, that LIKE was not optimized.
>
>Our tests showed partial optimization for LIKE clause and full optimization for =. Now, I thought I read somewhere that VFP 9 has full optimizable LIKE, but I don't remember, where I read it, so do not know, if it's true.
>
>I used SET ANSI OFF in order to use =, but I belive, it makes now everything not generic, since other back-ends use ANSI ON. So, it looks like I have to use LIKE clause if I want to easy switch back-end.
>
>What do you think about this?
>
>Thanks in advance.

Sorry for Jump in.

With SET ANSI OFF and = you write a two side LIKE 'item%' comparison,

expression1=expression2
is
expression1 LIKE 'expression2%' OR expression2 LIKE 'expression1%'

and it is impossible optimize it without a key index with variable lenght.

Before VFP9 in many situations VFP it optimized the query and it could return to you wrong results.

On VFP the LIKE 'expr%' can run with a full index filtering,
but exists two issues:
1. on VFP don't exists a escape character,
then for search a '%' or a '_' the LIKE is useless
2. on all backends the LIKE comparison uses the context Collate order,
as == ( ANSI ON VFP comparison),
but on VFP the LIKE uses MACHINE COLLATE,
and without a MACHINE index the optimization is not full.

Fabio