Is there a way to use MySQL fulltext to search related tables?

Question

I have a table called persons which contains data about, well, people. It also contains foreign keys to another table. I'd like to make a fulltext index that is able to search the related tables for full text.

Here is some sample data: (see http://sqlfiddle.com/#!9/036fc5/2)

CREATE TABLE IF NOT EXISTS `states` (
  `id` char(2) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `states` (`id`, `name`) VALUES
  ('NY', 'New York'),
  ('NJ', 'New Jersey'),
  ('CT', 'Connecticut'),
  ('PA', 'Pennsylvania');

CREATE TABLE IF NOT EXISTS `persons` (
  `id` int auto_increment NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `state_id` char(2) not null,
  PRIMARY KEY (`id`),
  FULLTEXT (first_name, last_name, state_id)
);
INSERT INTO `persons` (`first_name`, `last_name`, `state_id`) VALUES
  ('Arnold', 'Asher', 'NY'),
  ('Bert', 'Bertold', 'NJ'),
  ('Charlie', 'Chan', 'NJ'),
  ('Darrin', 'Darcy', 'CT');

So, I'd like to be able to search for persons from "Jersey", such as:

SELECT * FROM persons WHERE MATCH(first_name, last_name, state_id) AGAINST('Jersey');

But, of course, the text "Jersey" exists only in the states table and not in the persons table. Does it make sense to make a materialized/generated index? Is there a simpler way?

Answer 1

You need to put a separate full-text index on the states table, and join with that.

CREATE TABLE IF NOT EXISTS `states` (
  `id` char(2) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT (name)
);

CREATE TABLE IF NOT EXISTS `persons` (
  `id` int auto_increment NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `state_id` char(2) not null,
  PRIMARY KEY (`id`),
  FULLTEXT (first_name, last_name);

SELECT p.*
FROM persons p
JOIN states s ON s.id = p.state_id
WHERE MATCH(s.name) AGAINST ('Jersey')
UNION
SELECT *
FROM persons
WHERE MATCH(first_name, last_name) AGAINST ('Jersey')