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

Question

Hey, 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?

Answer 1

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.

Answer 2

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.