Full text search across many tables
I have three tables, each with a full text column. The user enters search terms into one text box and then all three tables are found.
This is better explained with an example:
documents
doc_id
name FULLTEXT
table2
id
doc_id
a_field FULLTEXT
table3
id
doc_id
another_field FULLTEXT
(I realize this looks silly, but that's because I removed all the other fields and tables to make it simpler).
So basically I want to do full text search in name
, a_field
and another_field
and then show the results as a list documents
, preferably with what caused this document to be found, eg. if it another_field
does, I would display that another_field
.
I started working on a system where three full text search queries are executed and the results are inserted into a table with a structure like:
search_results
table_name
row_id
score
(This could subsequently be done for cached results over several days, for example using a hash of search terms).
This idea has two problems. First, the same document can appear in search results up to three times with different ratings. Instead, if a search term is matched across two tables, it should have one result, but a higher score.
Second, the parsing of the results is difficult. I want to display a list of documents, but I don't know immediately doc_id
without any connection; however the table to join depends on the column table_name
and I am not sure how.
Wanting to search for multiple related tables like this should be common, so I'm guessing I'm asking if I'm going this the right way? Can anyone tell me the best way to do this please.
a source to share
I would create a denormalized single index. That is, all three types of documents are placed in one table with fields for doc_id, doc_type and one full-text block. Then you can search all three types of documents at once.
You may also find Lucene makes sense in this situation. This gives you faster searches as well as much more functionality around how search and grading works.
The downside is that you keep a separate denominated copy of the text for each record. The upside is that searches are much faster.
a source to share