searching through huge amounts of text -> very slow
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.