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)
.
FULLTEXT
index for the search purpose, but how to plan a subquery to insert the map?FULLTEXT
, you don't need the other two tables. See the docs on how to useMATCH ... AGAINST ...
FULLTEXT
is good for searching.