I have one large table (instant messages of my website users) with more than 200 000 records. The size of this table is > 140 MB. This table continues to grow - every day + 200-300 KB. I never had tables of size like this and my questions are:
1) How much records it's reccomended to keep in one table? Is it fine 2000000-3000000 records?
2) Is it a good idea to split this table to several smaller tables. If yes, what is the best way to do this:
- to keep instant messages in different tables depending of user id, for example:
if (($user_id > 0) and ($user_id < 7000)) {
$table = "messages1";
} elseif (($user_id > 7000) and ($user_id < 14000)) {
$table = "messages2";
etc...
- to keep instant messages in different tables depending of the first character in username, for example:
john = table_messages_j
michael = table_messages_m
Any other suggestions? I don't worry how much records are in this table but I think my website will became slow if I keep tables with millions of records. Isn't it?
