>I'm interested in any ideas for speeding up searches for words within a field. The typical syntax would be something like:
>
> select * from musictbl where "NIGHT" $ upper(title)
>
>but this takes forever on our table of 130,000+ records. On a 266mHz system it takes around a minute and a-half.
>
>Ideally I'd like to use something that would allow me to create a view. This leaves out PhDBase, which I have also found to be totally unreliable in VFP 6.
>
>My immediate solution has been to set up numerous fields to hold the individual words in a title, create indexes on those fields, and then construct a SQL that tests each of those fields:
>
> select * from musictbl where upper(t1)="NIGHT" or upper(t2)="NIGHT" or upper(t3)="NIGHT".... or upper(t20)="NIGHT"
>
>This is extremely fast (on that same 266mHz system it is virtually instantaneous) but easily creates SQL statements that are too long.
>
>Any ideas would be more than welcome!

Peter,
I used this years ago in fox2x where most of the computers ranged from 8086 to 286 in a multisuer environment. Their almost everytime search was on the same c(50) field. I created 50 indexes for the field !!!

for ix=1 to fsize('title')
 lcKey = 'upper(substr(title,'+str(ix)+'))'
 index on &lcKey tag ('search'+padl(ix,2,'0'))
endfor

It seemed dummy doing this to me too :) However even 8086's returned result set around 30secs max in a table with 75K recs !!!

Searcher part something like this :

lparameters tcSearch
tcSearch = upper(tcSearch)
create cursor myResults (inrec i)
select 0
use myTable again alias 'mySearch'
for ix=1 to fsize('title')
 	set order to tag ('search'+padl(ix,2,'0'))
 	lcKey = key()
 	if seek(tcSearch)
 	    scan while &lcKey = tcSearch
 	    	insert into myResults values (myTable.PKId)
 	    endscan
 	endif
endfor
use in 'mySearch'
select * from myTable ;
  where PkId in (select inrec from myResults) ;
  into cursor crsResult

BTW this is not the original code I used. There I was directly creating cursor with same structure as base and inserting the whole record which browsed at end.
Cetin