Extracting words from text field in SQL

Question

Hi, I'm currently building a little CMS for a smaller site. Now I want to extract all words from the text_content field and store them in my word table for later analysis.

page( id int, 
      title varchar(45),
      # ... a bunch of meta fields ...  
      html_content text,
      text_content text);

word( page_id int,        # Foreign key
      word varchar(100)); # I presume there are no words longer than 100 chars

Currently I'm using the following code, which runs very slowly (understandably) for larger chunks of text.

// Sidenote: $_POST is sanitized above scope of this code.
$_POST['text_content'] = str_replace("\t", "", 
         htmlspecialchars_decode(strip_tags($_POST['html_content'])));

// text is in swedish, so we add support for swedish vowels
$words = str_word_count($_POST['text_content'], 1, "åäöÅÄÖ");

// Delete all previous records of words
$this->db->delete("word", array('page_id' => $_POST['id']));

// Add current ones
foreach($words as $word)
{
    if (trim($word) == "")
     continue;

    $this->db->query("INSERT INTO word(page_id, word) VALUES(?, ?)", 
                      array($_POST['id'], strtolower(trim($word))));
}

Now, I'm not happy with this solution. I was thinking of creating a trigger in the database which would do pretty much the same thing as the php version. Is it possible to create a trigger in MySQL which would perform said actions, if so - how? Or is there a better way? Am I taking a crazy approach to this?

Answer 1

Triggers that perform large calculations will slow down your application.

I think you are better of scheduling a task to run periodically and perform the extraction for you.

Answer 2

Have you tried PHP's "htmlentities" function to strip of those tags?

Answer 3

You could make this PHP code significantly faster by building up a single insert query and executing it rather than a separate query for every word. Otherwise, I don't think your code looks that bad.