Searching large text fields [concluded]

[mendhak]

Suppose there's a MySQL table that has a BLOB field storing a huge amount of text. Further, suppose I were to perform a search on that table, looking for some specific word.

Would MySQL handle it well? What I imply by this question is, is it OK to have large text fields in a table, and perform queries on them, or would it be safer to have, instead of the large text field, a path to a text file containing all that text?

Tell me if I'm not clear enough.

[bekkel]

large databases make a seperated search table.
which contains a list of words which can be found in the 'blob'.
this to speed up things.

[phpman]

why are you going to put texct in a blob? that is for images, not text.

your search would be faster if you used a link instead.

[mendhak]

Sorry, I meant CLOB. MySQL has a CLOB, doesn't it?

[phpman]

no, never heard of clob

[mendhak]

OK, I went through mysql.com, and saw they have MEDIUMTEXT, TEXT, and LONGTEXT.


Anyways, I'll go with the "store a URL" option, until the search performance becomes a major issue with me.

Thank you.

[The Hobo]

How slow would it be to have MySQL search a TEXT field for words? I do it that way with my news management program and have never noticed any performance issues.

Of course, I've never really posted HUGE amounts of text, either.

[mendhak]

Originally posted by The Hobo
How slow would it be to have MySQL search a TEXT field for words? I do it that way with my news management program and have never noticed any performance issues.

Of course, I've never really posted HUGE amounts of text, either.

Can you paste an example of some news article you've posted here?

[kows]

this might be a bit late, but my site (http://david.complex-sys.com) has > 60 journal posts I've made that have obscene amounts of text. The search option (http://david.complex-sys.com/index.php?act=search_do) searches the articles for whatever you enter as a query. The entries are all stored in TEXT fields in my MySQL table, and I just query them all.. It's fairly efficient, and doing it in text files is going to take longer depending on how large the text file is. If it takes a while to open the text file, you'll have to wait, then you'll have to search it.. and now I just think I'm rambling, so, anyway, doing it with MySQL instead of a text file is going to make it faster.

[The Hobo]

Originally posted by mendhak
Can you paste an example of some news article you've posted here?

http://www.leftofreality.net/ is my personal site and has all the articles I've posted. Why do you ask?

[mendhak]

Originally posted by The Hobo
http://www.leftofreality.net/ is my personal site and has all the articles I've posted. Why do you ask?

I went through your site some (nice site, btw). The search seems perfectly fine. My articles are a little longer than that, usually, so I guess switching to TEXT would be safe enough.

I've never heard of the Super Hero named "Green Lantern." You've got an extensive amount of information for this one comic strip!

Thx.

[The Hobo]

Originally posted by mendhak
I've never heard of the Super Hero named "Green Lantern." You've got an extensive amount of information for this one comic strip!

Comic book. And at one time it was various comic books. Started in the 1940s. If you think I have a lot of information, you should see www.glcorps.org

Now THEY'RE obsessive.