Messages that are around 10k should be just fine, as long as they are the
majority.
A few tips for you... Store all the varchar/text columns for each message in
a seperate table so that the primary message table only has fixed length
fields (ie messageid, size, #of replies, userid, bodyid, ...). Then use the
"bodyid" field as an index to the table that contains the varchar/text
columns (you can do a left join on it when you need the bodies). This way it
will be very fast for mysql to find a row in the primary message table. Also
it will help you remember to not transfer the body unless you need it (try
to make your queries return the fewest number of rows that you can live with
because it reduces the strain on the server).
One thing to keep in mind about using mysql with something like this with
heavy load: inserts lock the table so you can not perform selects during
that time (same with updates). So if you are dumping/posting a lot of
messages into the table the select performance is going to suffer. If you
make the inserts low priority and have a bunch of load they may not get
inserted for quite a while. The innobase tables dont have this problem, but
I dont think they support mediumtext column types because they have a row
size limit (correct me if Im wrong).
Hope this helps.
ryan
> Is it efficient to store things in MEDIUMTEXT columns?
>
> I am developing a web based system (using perl and MySQL) for viewing an
> archive of mailing list messages. In my database, I have one row per
> message. The column called "message" is of type MEDIUMTEXT. My perl code
> retrieves it using $sth->fetchrow_hashref and then prints it. Message size
> is typically ~10K; sometimes it's as high as a few hundred K.
>
> Here's an example URL from my site:
>
> http://qd.anifics.com/browse.asp?y=1995&m=12&d=30
>
> I'm wondering if it's a good idea to use MEDIUMTEXT like this, or should I
> put the message body into individual separate files? It runs fine right
> now, but I'm worried that it won't scale up under significant load.
>
> -Philip Mak ([EMAIL PROTECTED])
>
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
