MySQL DB design question

B

brendon2020

[H]ard|Gawd
Joined
Dec 15, 2002
Messages
1,541
As some of you know i behind a project called Hawtmail and i'm running into some trouble with a few things. Something interesting though, type Hawtmail in google and my [H]Forum post comes up as #1. Anyway i'm busy trying to optimize Hawtmail cause i friend of mine will let me use his server so i can put this thing to the test (finally).

I've currently have 2 accounts each with about 20K emails to benchmark how the system will be once i let this thing go public. Problem is thats alotta emails and it makes my system crawl (p4 3.0ghz, 512ram) when i call mysql to count how many emails each account has.

So my question is, is this normal? This is the query,
msyql> SELECT mail_size FROM email WHERE user_ID='1';

12804 rows in set (33.26 sec)

there is an index on user_id with a cardinality of 7.

Do you think it would be best if i just had a cached the results or come up with just a whole way to store the users count in a cookie or just something along those lines.
 
Are you storing the mail in the same table? I might be reaching a bit here, but row size does have an impact on performance. If the message bodies are a tad on the large size, then try moving them out to another table.
 
what is the `mail_size` column? can you 'SELECT count(*) FROM ...', i believe that count(*) is specially optimized
 
:LJ: said:
Are you storing the mail in the same table? I might be reaching a bit here, but row size does have an impact on performance. If the message bodies are a tad on the large size, then try moving them out to another table.
Click to expand...

ah never thought that would matter but now that you mention it it makes sense, i'll move all the bodies to their own table since they're only called upon by id anyway. Thanks.
 
One other thing - it looks like you're using a string for your primary key. You'd be *much* better off using a bigint.
 
As LJ points out, you really should move the body of the email into a different table and you should use a integer as the key for your ids. Text fields in mysql are considered variable width fields and thus indexing (and searching) take FAR more time in tables of this type. Once you're split the tables, pulling the proper emails becomes a simple join.
 
You must log in or register to reply here.
Back
Top