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

On Tue, 19 Sep 2000, Steve Heaven wrote:

> 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.

It can:

campsites=> explain select * from camps4 where upper(city) like 'MACKINA%';
NOTICE: QUERY PLAN:

Index Scan using camps4_ucity on camps4 (cost=106.34 size=644 width=132)

In my particular case the like search needed to perform an upper() like
select, an index was created for this purpose (in this case: camps4_ucity)

Vince.

>
>
> 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)
>

--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev(at)michvhf(dot)com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================