MySQL - FULL TEXT INDEX ON FREQUENTLY UPDATED COLUMNS

Question

I have the following table design with a full text index and the following query:

 CREATE TABLE artists (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title varchar(255) NOT NULL,
    description text NOT NULL,
    category_id INT NOT NULL,
    -- some other columns excluded for brevity
    FULLTEXT idx (title, description),
    FOREIGN KEY fk_category(category_id) REFERENCES categories(id)
) ENGINE=InnoDB;


CREATE TABLE categories (
   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   name varchar(255) NOT NULL,
   INDEX name_idx (name)
) ENGINE=InnoDB;



SELECT *
  FROM artists
INNER JOIN  categories on categories.id = artists.category_id
WHERE categories.name = 'Rock' AND MATCH (artists.name, artists.description) AGAINST ('Michael Jackson' IN BOOLEAN MODE);

Now title and description are likely to be updated frequently. If I have thousands of records in the artist table, does that means the full text index will be recreated everytime a user edits a title or description or inserts a new record?

What would be a better design to accomodate such a scenario? Should I move the title and description fields into a separate table and will that make any difference?

Any advice would be appreciated. Note I know there a other methods to implement full text searching such as solr, elastic etc but I'm limited to the above at present.

Are you using MyISAM or InnoDB, and have you read the [documentation](https://dev.mysql.com/doc/refman/5.7/en/fulltext-fine-tuning.html) ? — Tim Biegeleisen, Jul 31 '17 at 10:10
@Tim its innodb as indicated in the posted code. The docs do not give any advise regarding this matter. — adam78, Jul 31 '17 at 10:29
I have upvoted your question. I hope some MySQL guru can help you. — Tim Biegeleisen, Jul 31 '17 at 10:30

score 0 · Answer 1 · answered Aug 14 '17 at 01:04

No INDEX is "rebuilt from scratch" whenever an INSERT or UPDATE occurs. Indexes are "incrementally" updated. And the Engine does some optimizations to minimize the cost of such updates.

By "updated frequently", do you mean that you are frequently changing the title of existing rows? Or do you mean that new rows are being added? I ask this question because there is some difference in the overhead between the two.

MySQL - FULL TEXT INDEX ON FREQUENTLY UPDATED COLUMNS

1 Answers1