How to create a FULLTEXT index for two tables in MySQL?

user_14224870 0
Viewed 245

I have two tables that look something like this:

Table #1:

CREATE TABLE iteminfo
(Code CHAR(1) PRIMARY KEY,
Tags TEXT NOT NULL);

Table #2:

CREATE TABLE items
(ID INT UNSIGNED PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Code CHAR(1),
FOREIGN KEY (Code) REFERENCES iteminfo(Code));

I want to create a FULLTEXT index using the fields Name and Tags from the two tables. I would assume that I will have to use EQUIJOIN or something similar but this doesn't work:

ALTER TABLE items JOIN iteminfo WHERE items.Code = iteminfo.Code
ADD FULLTEXT (Name, Tags);

I want to know:

Is this even possible to do? If yes, then how do I do it? If no, then what other ways are there to index two columns present in different tables?

Thanks for answering in advance! I apologise if this question already exists but I couldn't find the answer online.

indexing full-text-search mysql
2 Answers

No.

But... It would make sense to collect the various columns from the various tables together in a single column and apply a FULLTEXT index to it.

Assuming there are many tags for each item, you could initialize such via:

CREATE search_info ( PRIMARY KEY(name) )
SELECT name,
       CONCAT(name, ' ',
           ( SELECT GROUP_CONCAT(tags) FROM iteminfo
                      WHERE code = items.code ) ) AS search
    FROM items;

Then

ALTER TABLE search_info ADD FULLTEXT(search);

(After that, changes to items or iteminfo would need to also modify search_info.)

user_17668310

I tired what was suggested in @RickJames's answer and it seems to be the solution to my issue. For anyone else who might want to know, here's what I did but in context to the information I gave in my original question:

ALTER TABLE items ADD COLUMN Tags TEXT;

UPDATE items
SET search = CONCAT(name, ' ', (SELECT GROUP_CONCAT(tags) FROM iteminfo WHERE code = items.code))
WHERE Code IS NOT NULL;

ALTER TABLE items ADD FULLTEXT(Tags);
user_142248700
Related