MySQL Trigger With Full-Text Search

Question

I have a innodb table on mysql like this:

create table person (
    id int not null auto_increment primary key,
    name varchar(512),
    birthdate date,
    ...
    id_most_relevant int,
    fulltext(name)
);

I'm want to create a trigger that, whenever a person is updated, the trigger will search other person with most relevant name (by using a full-text search) and put his id on the id_most_relevant field of the updated person, but only if this relevance is more than 95%. So, to get the percentage relevance, I devide the relevance of each person with the relevance of the new name from the person updated. Something like:

SELECT id FROM PERSON
INTO _id_most_relevant
WHERE
    MATCH (name) AGAINST (_new_name) / 
    MATCH (new.name) AGAINST (_new_name) > 0.95

The variables _id_most_relevant and _new_name are previously declare on the trigger and the variable _id_most_relevant would be used to update the person table on id=NEW.id .

Anyone has an idea on how to do that trigger?

I'm using Mysql 5.6 and I can't update it, but I can create an auxiliary table if necessary.

Answer 1

I find out that is not possible to solve this problem using a trigger because whenever I update a person's name I will need the fulltext index already updated to get the second MATCH (on the denominator), but the fulltext index it only be updated after the tigger executes (even if it was a after update trigger).

So I have to solve this using other way, without triggers.

That said, I will close this post.