MySQL full text search across multiple tables
Solution 1
MySQL can't make a fulltext (or any) index accross multiple tables. So using a single index is out.
As an alternative, you could either:
Use an index on each table, and a join/union as appropriate to retrieve the rows that match your requirements.
Create an aggregate table to apply the index to.
Use a tool such as lucene or solr to provide your search index. (If you are going for any sort of scale, this is likely the best option)
Solution 2
Add the relevance scores together:
SELECT ID, Title, Description, Author,
MATCH (Title) AGAINST ("search terms") +
MATCH (Tags) AGAINST ("search terms") +
MATCH (Body) AGAINST ("search terms")
AS Relevance
Solution 3
simply do:
select * from table a where a.col=myval
union
select * from table b where b.col=myval
..
indices are used as they are with a normal select.
Solution 4
With your setup being what appears to be a type of message board I assume that you have three tables (correct me if I am wrong):
- Message Table (Message_ID, Title, Body, Description, Author)
- Tag Table (Tag_ID, Name)
- Message Tags (Message_ID, Tag_ID)
Here is how I would do it
SELECT Message.Message_ID, Message.Title, Message.Description, Message.Author,
IFNULL(
MATCH (Name)
AGAINST (?)
,
IFNULL(
MATCH (Message.Title)
AGAINST (?)
,
MATCH (Message.Body)
AGAINST (?)
)
) AS Relevance
FROM Message, Tag, Message_Tag
WHERE Message.Message_ID = Message_Tag.Message_ID AND Message_Tag.Tag_ID = Tag.Tag_ID
AND (
MATCH (Name)
AGAINST (?)
OR
MATCH (Message.Title)
AGAINST (?)
OR
MATCH (Message.Body)
AGAINST (?)
)
Samuel
Updated on June 14, 2022Comments
-
Samuel almost 2 years
I have a series of tables that contain data I want to full text search. I've tried combining the tables with
UNION
, but the result loses its fulltext index so can't be fulltext searched. I don't think that putting the data into a temp table is the way to go. Is there someway that I can fulltext search these tables efficiently? Thanks in advance!UPDATE: my query for fulltext was
SELECT ID, Title, Description, Author, MATCH (Title,Tags,Body) AGAINST ("search terms") AS Relevance FROM [combination of tables goes here] WHERE MATCH (Title,Tags,Body) AGAINST ("search terms")
-
Justin Giboney almost 15 yearsAre you looking for the same thing in the different tables or are you looking for different things? How many tables are you including?
-
-
Rytmis almost 15 yearsAt least in the 5.x branch, joins that use indices seem to have the side effect that the fulltext index can't be used. Makes the queries rather inefficient. :/
-
benlumley almost 15 yearsThats interesting - guess its a side effect of mysql only being able to use one index per table. Never really thought about that in the context of fulltext searching.
-
Samuel almost 15 yearsnum one is what i was trying to do, but the indexs vanish after union, num two is what i didn't want to have to do. thanks for confirming what i was beginning to understand