How to store data in mysql, to get the fastest performance?

I'm thinking about it, which of the following two query types would give me the fastest performance for a user messaging module inside my site:

The first one i thought about is a multi table setup, which has a connection table, and a main table. The connection table holds the connection between accounts, and the messaging table.

In this case a query would look like following, to get some data of the author, and the messages he has sent:

SELECT m.*, a.username 
  FROM messages AS m
  LEFT JOIN connection_table 
    ON (message_id = m.id)
  LEFT JOIN accounts AS a 
    ON (account_id = a.id)
 WHERE m.id = '32341'

Inserting into it is a little bit more "complicated".

My other idea, and in my thought the better solution of this problem is that i store the data i would use in a connection table in the same table where is store the data of the mail. Sounds like i would get lots of duplicated entries, but no, because i have a field which has text type and holds user ids like this: *24*32*249* If I开发者_如何学运维 want to query them, i use the mysql LIKE method. Deleting is an other problem, but for this i have one more field where i store who has deleted the post. Sad about that i don't know how to join this.

So what would you recommend? Are there other ways?

Sounds like you are using an n:m relation.. if yes, don't put a list of ids in a single column but create a mapping table containing two columns - the primary key of table1 and the primary key of table2. Then selecting, inserting and deleting will all be easy and still cheap.

I wonder how many messages will be send to multiple recipients? It might just be easier to have it all in one table - MessageID, SentFrom, SentTo, Message, and dup it for multiple people. This obviously makes it extremely easy to query.

Definately avoid storing multiple ID's in one field and using LIKE - that'll be a performance killer - go with ThiefMasters suggestion if you want something like that.