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

5
  • Have a look at Full text search functions for MySql.
    – McNets
    Commented Jun 7, 2017 at 17:24
  • 1
    @McNets I have FULLTEXT index for the search purpose, but how to plan a subquery to insert the map?
    – Googlebot
    Commented Jun 7, 2017 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
    Commented Jun 8, 2017 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
    Commented Jun 8, 2017 at 20:45
  • For key-value, you do not need a many:many mapping table.
    – Rick James
    Commented Jun 8, 2017 at 21:43

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.