Re: Is it possible to search for sub-strings...

At 09:00 19/09/00 -0700, Mitch Vincent wrote:
>LIKE can use indexes, ~* can not, that's LIKE's main advantage (at least in
>my opinion).
>

~ does use indexes, ~* doesnt,
but then nor does the equivalent upper(columnname) LIKE 'TERM'. Make sure
you're comparing apples with apples.

explain select * from all_title_fti where string like 'A%';
NOTICE: QUERY PLAN:

Index Scan using all_title_idx on all_title_fti (cost=86633.57 rows=1
width=16)

explain select * from all_title_fti where string ~ '^A';
NOTICE: QUERY PLAN:

Index Scan using all_title_idx on all_title_fti (cost=86633.57 rows=1
width=16

explain select * from all_title_fti where upper(string) like 'A%';
NOTICE: QUERY PLAN:

Seq Scan on all_title_fti (cost=170921.58 rows=1083414 width=16)
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk