MYSQL matching one column to another in a different table via a FULLTEXT index

Question

I current have two different tables.

The first table has a list of titles and IDs associated to these titles; the second table is a list of random heading.

What I would like to know is if there is a way to match up all the titles in table 2 to the closest matching title in table 1. Is this possible?

I've tried :

SELECT title_table .*,
 random_titles.*, 
 MATCH (title_table.title) AGAINST (random_titles.title) AS relevance 
FROM title_table 
ORDER BY `relevance` DESC

But that did not work.

I know I could use this query as each title is being put in table 2 with PHP but I already have a lot of titles in the database.

Answer 1

AGAINST() only accepts a string literal or a variable containing a string -- something the optimizer can deterministically resolve to a constant.

AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

^{-- http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html}

This rules out any construct I can think of where an ordinary query could be used to join tables based on a fulltext index match.

...however...

You can wrap the fulltext query in a stored function:

DELIMITER $$
DROP FUNCTION IF EXISTS `best_fulltext_match` $$
CREATE FUNCTION `best_fulltext_match` (search_string TEXT) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
 RETURN (SELECT id FROM title_table WHERE MATCH(title) AGAINST(search_string)
 ORDER BY MATCH(title) AGAINST (search_string) DESC LIMIT 1);
END $$
DELIMITER ;

Now...

SELECT best_fulltext_match('your title here');

...returns the id of the best match according to the fulltext index in title_table, and this function will accept a variable, no problem.

You could then use this to update your random_title_table.

UPDATE random_title
 SET title_id = best_fulltext_match(title);

The function is invoked once per row in random_title, and gives the fulltext search a static value to search with.

You could also use that same function when creating new entries in random_title.

Answer 2

The "the closest matching" constraint make me think to the MySQL function SOUNDEX .

I never used this feature but maybe it's well designed for you :)

Max.