database search question...

["Hatzenbeler, Tim" <thatzenbeler@xxxxxxxxxxxxx>]

Title: database search question...

How does ebay do it?  And can the as/400 do it?  Or do I just need a super fast computer with a ton of memory...

The question is this....

I have a product file...  About 20,000 items... And I have end users who want to be able to preform partial word searches on the description field...  Seems simple enough, but the problem is speed...

If I do an rpg program, that reads every line of the product file and does a %scan function... It works, but its not very fast...

I could to an SQL with the  like '%fuzzy%' but even thats slow... and if they gave it a real generic lookup value, it will take forever...

So my question is this, does any one have a suggestion?  My 1st thought is something like this.   Program a word index creator, that  would run nighlty.

Product catalog
item#,description
1234, big red car
1235, little green car

and then a lookup data base, would look like this

search word, part#
---------------------------
big,1234
car,1234
car,1235
green,1235
little,1235
red,1234

and then if someone wanted [little car], it would do a search on [little] and [car] and if both are positive to the same key(item#), then pull that record...

I'm hoping someone could think of a better way...  I'm hoping there is an os/400 database command to solve this problem, so that's why i'm posting it in this list.

If not, the index creator doesn't seem too hard.

Thanks, tim