How to split data over MySQL tables

Question

I have a website with members who message each other. There are getting to be a few members and they like to send messages - I'm sure you can see where this is going.

Currently I have said messages stored in a nicely relational table cunningly titled "messages" with different status ids to denote, er, status (unread, saved, etc). I know this is after the fact, but I'm thinking that I really need to split this table into several others (as in more than one per status type, for instance) and I'm not sure what the best way is to go about it.

I have a couple of ideas, none of which are rocket science, but I'm curious if this has a 'standard solution'. Google suggests not, but then these kinds of issues aren't that common outside of places like stackoverflow, I imagine.

Anyone been-there-done-that already?

Answer 1

Have you thought of possibly having an archive process that would archive anything older then a certain period of time?

With proper indexing and fine tuning you would have to have quite a few messages to require moving them across multiple tables, unless its a issue with space.

Answer 2

There is nothing wrong with having a messages table with lots of data. No reason to split it if you don't really need it.

If you are concerned about the performance of this table, first choose your storage engine wisely depending on your needs : my advice would be using InnoDB since it will be write-intensive. Then look into indexes in order to speed up your reads.

What you could do if the table really gets too large and slow, is to create 2 tables :

• a messages_archive table, with MyISAM storage (only used for fast retrieving and searching of "archived" messages).

• a messages_inbox table, with InnoDB storage : this is the table where new messages are inserted frequently.

Optional :

• a messages_drafts, keeping auto-saved messages. Reason : no need to slow up the inbox table with drafts messages.

Btw, this is not a "standard solution" as you said, just an idea. :)

EDIT :

From your comment, I understand that what you are looking for is actually "Partitioning".

Different MySQL partition types allow you to physically segregate data by range, list, key or hash.

Answer 3

I would definately split the 'messages' table into multiple. Eg:

MessageStatus Message MessageText

This way if your displaying a list of items in someone inbox, you only need to scan the 'Message' table which is smaller and fixed length columns for max seek speed. When someone wants to open and see the message body you then hit the 'MessageText' table. The 'Message Status' is just a lookup table to join with a tinyint FK'd to the 'Message' table.

You'll have much more performance over 1 table with probably a mediumtext column.