I have a current Private Message system, hand rolled. I built it sometime around 2004, and even though I've made several cosmetic updates since then, there's been no major backend overhaul.

I want to change things up, make the system a little more modern. And I figure that now's the time to work on performance improvements, too :-)

My current system
I have one table for messages, with columns:

id
username
folder
status (ENUM('New', NULL)
postdate
sender
subject
message

I have a second table for images, with columns:

id
sorter (0-9)
image

For the list of messages, this is the query in PHP:

SELECT private_messages.id, username, folder, status, postdate, sender, subject, message, pm_img.image
 FROM private_messages
 LEFT JOIN pm_img
 ON
  pm_img.id = private_messages.id AND
  pm_img.sorter = 0
 WHERE 
 username = '%s' AND 
 folder = '%s'
 ORDER BY postdate DESC
 LIMIT 21;

Then when they view the message, it's simply:

SELECT * FROM private_messages WHERE id='%s' LIMIT 1

My updated plan
I plan to eliminate "subject" entirely, and group together all messages from the same sender so that it's more like a text chain.

I think that I can eliminate the JOIN altogether, too; no need to spend resources just to show an image of a paperclip to denote an attachment if nobody really cares anymore.

My options that I'm considering are:

Option 1. Modify the existing list query to something like:

SELECT id, status, postdate, sender, message
 FROM private_messages
 WHERE 
 username = '%s' AND 
 (folder = '%s' or folder = 'sent')
 GROUP BY sender
 ORDER BY postdate DESC
 LIMIT 21;

That doesn't quite work right (it's not including all of the senders), but you get the idea.

Option 2. Create a second table that stores the info for the most recent message sent per user, and only query it for the list:

# would have a UNIQUE on username, folder, and sender
SELECT id, status, postdate, sender, message
 FROM pm_list
 WHERE 
 username = '%s' AND 
 (folder = '%s' or folder = 'sent')
 ORDER BY postdate DESC
 LIMIT 21;

Then I could eliminate "status" from private_messages.

The second option should process faster, since "list" would have a much smaller table (and index) to query, and I could eliminate some data and one index from private_messages.

BUT, in the rare case that I have to moderate something (eg, a scammer comes on and sends 1,000 messages while I'm asleep) it would be a lot harder to clean up. Right now all I have to do is delete the rows from private_messages, but if I use option (2) then I'd have to delete them AND update pm_list for each user.

Which would you do? Or is there a third option I should consider?