1

Having the database structure that follows:

CREATE TABLE articles
(
article_id int(11) unsigned NOT NULL AUTO_INCREMENT,
article text,
PRIMARY KEY(article_id)
) ENGINE=InnoDB;

CREATE TABLE words
(
word_id int(11) unsigned NOT NULL AUTO_INCREMENT,
word varchar(255),
PRIMARY KEY(word_id)
) ENGINE=InnoDB;

CREATE TABLE word_map
(
article_id int(11) unsigned NOT NULL,
word_id int(11) unsigned NOT NULL,
FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE,
FOREIGN KEY(word_id) REFERENCES words(word_id) ON DELETE CASCADE,
PRIMARY KEY(article_id, word_id)
) ENGINE=InnoDB;

How can I split the text column in the article table to find the words which exist in the words table and, then, INSERT the words into word_map?

Now I am doing this in PHP by splitting the text into words, then, finding word_id for each word, and then, INSERT INTO word_map (article_id, word_id).

  • Have a look at Full text search functions for MySql. – McNets Jun 7 '17 at 17:24
  • 1
    @McNets I have FULLTEXT index for the search purpose, but how to plan a subquery to insert the map? – Googlebot Jun 7 '17 at 18:16
  • If you have FULLTEXT, you don't need the other two tables. See the docs on how to use MATCH ... AGAINST ... – Rick James Jun 8 '17 at 18:37
  • 1
    @RickJames why not? two other tables can have other useful columns such as various counts, status, tracking, etc. FULLTEXT is good for searching. – Googlebot Jun 8 '17 at 20:45
  • For key-value, you do not need a many:many mapping table. – Rick James Jun 8 '17 at 21:43

Your Answer

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

Browse other questions tagged or ask your own question.