Searching TEXT fields

[Michi]

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+)?

[Lüpher Cypher]

Michi wrote:

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+)?

Use regular expressions :)
SELECT * FROM forum WHERE message REGEXP 'text'

[Michi]

Lüpher Cypher <lu***********@verizon.net> wrote in message news:<N1kcd.868$WN5.765@trndny08>...

Michi wrote:

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+)?

Use regular expressions :)
SELECT * FROM forum WHERE message REGEXP 'text'

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...?