Re: Full Text search on multiple tables?
Posted by:
parts1
Date: January 05, 2006 06:23PM
I had the same problem, and the way I got around it was:
create a table that has columns of: tablename, score, rowid
Then, add to this table the results from your FULLTEXT search on your first table, just the rowid, the name of the table, and the score.
Do this for all tables you want to search.
Then run a query sorting the results in this table by score, then get the actual data from the other tables.
This way it will search the tables you want to include, and sort them based on relevance.
I also added into my temporary table a hash field, and generated that based on the search term, so that the results could be cached and used again if the exact same query was performed. I started off emptying the table after every query, but now I don't bother.
Just my 2c worth, hope it helps.