SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
-
May 9, 2003, 02:03 #1
- Join Date
- Oct 2001
- Location
- romania
- Posts
- 44
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
searching through huge amounts of text -> very slow
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.you'll never be what you desire
-
May 9, 2003, 04:41 #2
If you want an exact match you could use WHERE something = 'something' instead of using LIKE
SeanHarry Potter
-- You lived inside my world so softly
-- Protected only by the kindness of your nature
-
May 9, 2003, 05:13 #3
- Join Date
- Oct 2001
- Location
- romania
- Posts
- 44
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Oh yeah? Don't think so ...
How can a big story, let's say a tutorial, be equal to a word ?!?!?!?!?!you'll never be what you desire
-
May 9, 2003, 05:21 #4
Oh crap, not with it today! Sorry. My next suggestion is use fulltext indexing
Code:SELECT something FROM table WHERE MATCH (field) AGAINST ('+search_term_1 +search_term_2' IN BOOLEAN MODE);
SeanHarry Potter
-- You lived inside my world so softly
-- Protected only by the kindness of your nature
Bookmarks