Fulltext Keyword search across multiple tables should I do...

[cooldude832]

I have 3 tables that all relate back to a user

Users Table (user data email, address, phone etc.)

Relationships (user-user link dates, etc.)

Employment records (who they are employed by, dates, position etc.)

 

The goal is to full text search any field in any table pertaining to a user

so if the keywords are oranges, Florida and farmer it will match a person who lives in Florida, worked in Florida, worked with oranges or is a farmer.

 

What is the best way to accomplish such a complex query?

There will be around 50+ fields that will be looking for "full text"

[mkoga]

One way to handle such a complex search is to index them with lucene. The zend framework has an implementation of it: http://framework.zend.com/manual/en/zend.search.lucene.html along with tutorials.

[mwasif]

You can not have multiple table fulltext index in mysql. You might need to look at http://www.sphinxsearch.com/.

[cooldude832]

You can not have multiple table fulltext index in mysql. You might need to look at http://www.sphinxsearch.com/.

 

I sure can fulltext across multiple tables I may need to use multiple queries but it can be done

[fenway]

You can not have multiple table fulltext index in mysql. You might need to look at http://www.sphinxsearch.com/.

 

I sure can fulltext across multiple tables I may need to use multiple queries but it can be done

It can't be done without multiple queries... but that's not going to "use" an index.

[cooldude832]

well Ideally I want to have the keywords found be highlighted in the search results so if you matched former job it would say

former job: <b> Orange</b> Picker

 

but the thing is there is about 50 fields not to mention there can be hundreds of relationships and jobs to be queried off for each user row.

 

My original thought was each time a usersID is hit in any table to recompile a field for the user that contains their "Info" a formatted field that would then be full searched.  And then if that hits full text I would then use php to treat it as a csv file for say.  Is that a good idea or no?