Single mysql table for private messaging

Question

I'm trying to create a single table for private messaging on a website. I created the following table which I think is efficient but I would really appreciate some feedback.

CREATE TABLE IF NOT EXISTS `pm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `to` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `subject` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `read` tinyint(1) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
  FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I have 2 columns that determine the status of the message: read and deleted

If read = 1, the message has been read by the receiver. If deleted = 1, either the sender or the receiver deleted the message from the sent or received inbox. If deleted = 2 both users deleted the message, therefor delete the row from the database table.

Answer 1

I see that you don't have have any indexes explicitly stated. Having the appropriate indexes on your table could improve your performance significantly. I also believe that for your message column you may want to consider making i a varchar with a max size explicitly stated. Other than those two items which you may already taken care of your table looks pretty good to me.

MySQL Table Performance Guidelines:

1. Add appropriate indexes to tables. Indexes aren't just for primary/unique keys add them to frequently referenced columns.
2. Explicitly state maximum lengths. Fixed length tables are faster than their counterpart
3. Always have an id column.
4. Add NOT NULL where ever you can. The nulls still take up space
5. Know your data types. Knowledge is power and can save on performance and space

Interesting Articles:
VarChar/TEXT Benchmarks
Similar Question
Some Best Practices
Data Type Storage Requirements

The articles and some of the items I have listed may not be 100% correct or reliable so make sure you do a bit of your own research if you are interested in further tuning your performance.

Answer 2

A few comments:

Charset=latin1 is going to piss some people of I'd suggest charset=utf8.

I'd suggest putting a foreign key check in not only on user_id, but on to as well.

Also I'd put an index on date, as you will be doing a lot of sorting on that field.

You need to split deleted in two fields, otherwise you will not know which user has deleted the message. (deleted_by_user, deleted_by_recipient)

Note that date is a reserved word and you'll need to change it into message_date or `backtick` it in your queries.