searching through huge amounts of text -> very slow

[lupulet]

hi.
I have a problem. I have a table which has a column called "text". OK. The problem is that the table has 6000+ rows, each one of them storinf in "text" a large amount of text, something like a story. For a better idea, the table has 40Mb (the other columns are insignificant).

Now, when i try to search through table rows that have in this column text that match a word, no problemo:

SELECT text FROM table WHERE text LIKE "%word%";

But ... when i try to search through table rows that have in this column text that contains exactly a word, the only solution that i know is to use REGEXP like this:

SELECT text FROM table WHERE text REGEXP "[[:]]word[[:>:]]";

Welllllll, that's far too slow, about 5 time slower than example 1. And I really need that exact search through those stories after a word!!!

What should i do? Any idea? Thanks.

[seanf]

If you want an exact match you could use WHERE something = 'something' instead of using LIKE

Sean

[lupulet]

Oh yeah? Don't think so ...

How can a big story, let's say a tutorial, be equal to a word ?!?!?!?!?!

[seanf]

Oh crap, not with it today! Sorry. My next suggestion is use fulltext indexing

SELECT something FROM table WHERE MATCH (field) AGAINST ('+search_term_1 +search_term_2' IN BOOLEAN MODE);

http://www.mysql.com/doc/en/Fulltext_Search.html

Sean