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