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 .*,
          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.


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.


You can wrap the fulltext query in a stored function:


DROP FUNCTION IF EXISTS `best_fulltext_match` $$
CREATE FUNCTION `best_fulltext_match` (search_string TEXT) RETURNS INT

  RETURN (SELECT id FROM title_table WHERE MATCH(title) AGAINST(search_string)
           ORDER BY MATCH(title) AGAINST (search_string) DESC LIMIT 1);

END $$



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.


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 :)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.