By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,230 Members | 2,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,230 IT Pros & Developers. It's quick & easy.

Searching TEXT fields

P: n/a
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+)?
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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'
Jul 20 '05 #2

P: n/a
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...?
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.