| re: Searching TEXT fields
Lüpher Cypher <lupher.cypher@verizon.net> wrote in message news:<N1kcd.868$WN5.765@trndny08>...[color=blue]
> Michi wrote:[color=green]
> > Hello,
> >
> > I am creating a databse with a large number of text (or blob?)
> > entries. I want users to be able to search these fields.
> >
> > An example would be a forum or journal. Indexing every word in the
> > post seems costly. If I have 100,000 posts over a few months, and each
> > one has 50 words in it, that is a 500,000 match entries. The databse I
> > will be creating will be even larger. For longer posts, there is a
> > huge overhead of dozens of INSERT commands, which would then further
> > slow any search attempts (write locking).
> >
> > Is there a better method for indexing free-style text entries so they
> > are readibly searchable? Also, the above mentioned method doesn't
> > allow much wiggle room for "exact phrase" searching. What is the best
> > way to approach this problem for a large scale database (500,000
> > rows+)?[/color]
>
> Use regular expressions :)
> SELECT * FROM forum WHERE message REGEXP 'text'[/color]
That seems highly inefficient. If I were to search for a word through
500,000 rows, it would take the full time of having to read and
process every row. I might as well not index anything. I am trying to
see what is the best enterprise solution to search text fields. I
could use match() AGAINST fields and do full text searching...?
| 
