|
|
Posted by Jerry Stuckle on 03/29/06 19:16
Roman Ziak wrote:
> 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
Roman,
But it WILL slow down the database.
Everything on one table is typically kept in a single file. So, let's say
you're going to list the received messages for 'johndoe'. Are you going to
display all the message text with each one? Typically they display a header -
only to, from, subject and date sent (and maybe a message number). A query
would look like:
SELECT from, subject, sentdate
FROM messages
WHERE to = 'johndoe';
Now - it can use an index to locate the records where to = 'johndoe'. No
problem there. However, it must still read the table to get subject and sent date.
If the message text is in the same table, it still has to read the message text
(or at least part of it), even though it wasn't requested. And there is more
data to buffer, making it less likely that the next message is in the same
physical block of data.
OTOH, if the message text is in a different table, it doesn't have to be read
with the rest of the header information. There is a greater likelihood that the
next message is in the same buffer. And more message headers can be buffered in
the same amount of memory.
Of course, there will be slightly more overhead when you fetch the text of the
message because you now have to join two tables. But in a typical system that's
not done nearly as often as displaying a list of messages.
Just because you're not returning all the data doesn't mean there isn't
additional overhead.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|