Re: like & optimization

On 12/10/13 20:08, Scott Ribe wrote:
> select * from test where tz >= start and tz < end and colb like '%foobar%'

I think you can use an index only for wildcard expressions that are
anchored at the beginning. So,

  select * from test where tz >= start and tz < end
     and colb like 'foobar%'

can use an index on colb.

You could perhaps

  select * from test where tz >= start and tz < end
     and colb like 'foobar%'
  union all
  select * from test where tz >= start and tz < end
     and reverse(colb) like 'raboof%'

Then you need 2 indexes, one on colb the other on reverse(colb).

You can have duplicates in the result set if the table contains rows
where colb='foobar'. If that's a problem, use union distinct.

Alternatively, if foobar is kind of a word (with boundaries), you could
consider full-text search.

Just my 2¢,
Torsten