Split large tables

I have a heavily used message system on my site (mysql 4 & php4). The
site is running on a dedicated server with two dualcore opterons and
4GB RAM and raid.

At the moment I have one table for sent items and one inbox table
(storage time 45 days/deletion of older messages and table optimize
every night). At the moment both tables getting near 1 Gigabyte. For
selects I only use the ID or the owners name. On both fields are
indexes. (sorting is only possible by id, newest <-> oldest). With over
600 concurrent users on the site the performance is well at this point.
But the mysqld prozess sometimes uses a lot/most of the cpu-%. Slow
query log is empty.

Now I would like to add more functions to the message area (like more
sort options, order by threads, flags, folders). That will make the
select statements more complex and I'm thinking of possibilities to
optimize the database layout. Also the database is growing and in some
month the table will be 2 GB in size.

The easiest way in my opinion is just to split the tables. Maybe for
every usernames first character (A-Z and 0-9). Then I would have 36
tables for inbox and 36 for sent items (maybe I will put inbox and
outbox in one table). But in this szenario I will have a lot more open
tables but fewer locks. Another advantage could be that if at this
point the large table fails it affects all users. Also a repair lasts
longer. I also read something about InnoDB but i don't know that much.

I appreciate any ideas. Thanks in advance

"Daniel Onnen" <itroxx@gmx.de> wrote in message
news:1141806653.641277.312190@j52g2000cwj.googlegr oups.com...
> Now I would like to add more functions to the message area (like more
> sort options, order by threads, flags, folders). That will make the
> select statements more complex and I'm thinking of possibilities to
> optimize the database layout. Also the database is growing and in some
> month the table will be 2 GB in size.
>
> The easiest way in my opinion is just to split the tables.

I wouldn't do that as an initial step to optimize. It will make your
application code more complex because you'll have to query a different table
based on the username.

I'd recommend indexing the other fields you may need to search/sort on, and
then increase your key buffer (since you have 4GB of RAM, go ahead and use
some of it for caching indexes).

Note that MySQL has a limitation that it can use only one index per table
per query. Your application would benefit greatly from the use of multiple
indexes (e.g. restrict rows based on userid, then sort on flags field).
Have you considered other RDBMS brands, like PostgreSQL or Firebird or
Oracle?

Regards,
Bill K.