On Wed, 18 Apr 2001, ryc wrote:
> 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, ...).
I have some VARCHAR columns (name, e-mail address and subject) that are
used in every query, though. Should I change them to CHAR(255) so that I
can do this? (On a related note, does anyone know whether the RFC for
e-mail messages limit the subject length/name length/e-mail address length
to a certain number of characters? What is this number?)
Also, is it a good idea to make a field called size and use that instead
of calling length(message) (message is MEDIUMTEXT) when I don't need to
retrieve message, or does MySQL have length(message) cached?
...
I just ran a crude benchmark on three different ways to retrieve text from
the database. The time required for 100 iterations of each method is:
DB2: 3.7 seconds
DB3: 11.5 seconds
Text: 5.6 seconds
DB2 is where I retrieve the message from the database:
my $sth = query("SELECT * FROM ffml WHERE num=6051");
my $this = $sth->fetchrow_hashref;
print $this->{message};
DB3 is where I retrieve the message from the database and split it:
my $sth = query("SELECT * FROM ffml WHERE num=6051");
my $this = $sth->fetchrow_hashref;
my @message = split("\n", $this->{message});
for my $line (@message) { print $line."\n"; }
Text is where I retrieve the message from a disk file:
open(MSG, '6051');
while ($line = <MSG>) { print $line."\n"; }
So it would appear that retrieving a message (the message that I used in
this test is 300k, about the largest that is in the archive) from the
database is faster than loading it from a disk file. However, when I have
to split() the message into a list (one entry per line), it becomes
slower. Any idea how I can split it more efficiently? (My code has to
process each line individually in order to hilight text from quoted
replies; e.g. look at the different colors in
http://qd.anifics.com/read.asp?num=32)
> So if you are dumping/posting a lot of messages into the table the
> select performance is going to suffer.
Inserts shouldn't be too common; just once every time the mailing list
receives a new message (no more than 100 per day).
Thanks,
-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
