Implementing versatile text search

Hi,

I have an struts application that lists a large number of products held in a mysql database. Due to the large number of items I need a fairly powerful method of searching for items based on full or partial matches. Each product's detail is represented over a series of fields, i.e general description, specific description, item number etc...

I need to be able to handle users searching for something like '424 foil' where '424' may be a substring of the specific description and 'foil' may be a substring of the general description. Although not neccessary limited to just two fields. I also need to include in the results where maybe only one or two search string words were matched, that is not all of them.

I guess I would need first to parse the search string into its component words but from here on I'm not sure what's an efficient way to handle a search. I suspect some form of concurrent representation of the system product catalogue optimised for text searches would be required.

If anyone has handled something similar or heard of an approach please share

Thanks, Andrew

You might take a look at Lucene (from Apache http://jakarta.apache.org/lucene/docs/index.html). It uses its own datastore so you'd have to push the string fields into it.