|
|
Posted by Roman Ziak on 03/29/06 16:45
Jerry Stuckle wrote:
> chris.withers@gmail.com wrote:
>> I have built a web portal and was considering adding a 'user mail'
>> feature for users to message each other. I'm a bit uncertain on how to
>> design the DB, so was wondering if there is a standard to do this. I
>> was figuring perhaps a table storing all user messages (but this might
>> become very large very fast?) or would a seperate db storing
>
> [snip]
>
> If I were doing it, I would keep the body of the message in a separate
> table, something like:
>
> First table:
> msgid
> from
> to
> date
> subject
> status (sent/received/saved)
>
> Second table:
> msgid
> msgtext
>
> The reason for keeping the message itself separate is speed. As you
> note, the file could become very big. You don't want the messages every
> time you scan - for instance, when you display the list of messages
> available to the user. Keeping the text in a separate table will speed
> up these requests, at least with MySQL. Of course, displaying the
> actual message will be slightly slower, but you won't be doing that as
> much as just displaying to/from/subject.
>
Jerry, can you elaborate on the field msgtext slowing down MySQL ?
Say for inbox retrieval for user "johndoe" we use:
SELECT msgid,from,to,date,subject,status
FROM messages
WHERE to='johndoe'
instead of
SELECT *
FROM messages
WHERE to='johndoe'
(It will be more appropriate to use user's id for "from" and "to"
instead of name. Also one would want to use slightly different field
names because some of the suggested ones are SQL keywords).
I am assuming indexed "from" and "to" queries, so db engine will most of
the time work on these indexes instead of crunching through the table.
Indexes will slow down the INSERT but that will be used less often than
SELECT.
In my opinion the field "msgtext" should not load the database if it is
not required in the query, but I am by no means a database expert. Maybe
an idea for quick experiment :)
Roman
Navigation:
[Reply to this message]
|