Optimization dilema

Question

What would be the most optimized way? I'm building a kind of "social network". The users can upload photo albums, files or messages. Each one of this data is saved in a different table. The receivers are saved as their userId separated by a comma, as such: "1,2,3,4,". Now I want to grab everything that was sent for a user. I have 2 ways (that I know of) to do so.

Fetch each table separately(Not very efficient since I'll have to do a query for each row fetched to get the senders user from another table). And then process the data.
Join the 3 tables. But from what I understand I cant use LIKE in a JOIN.

Any ideas?

I am not 100% sure about what you are asking but storing `1,2,3,4,etc...` is a terrible idea. — MonkeyZeus, Apr 14 '14 at 19:41

score 1 · Answer 1 · edited May 23 '17 at 12:21

1

You can use LIKE in a JOIN (see how to use a like with a join in sql?), but if I understand you properly, you shouldn't represent your receivers as a comma-separated string list. Instead, use another table with a separate record for every receiver-item relationship, so that you can query on it without needing to use LIKE.

edited May 23 '17 at 12:21

Community

1
1

answered Apr 14 '14 at 19:10

Brian Kendig

2,452
2
26
36

score 1 · Answer 2 · answered Apr 14 '14 at 19:12

1

You can use like in a join. The expression that you are looking for is something like:

on find_in_set(a.value, b.list) > 0

However, this is not recommended, because MySQL cannot optimize the performance of the query using indexes. In other words, the items that you have in a list should be separate rows in a junction/association table.

If you need to do joins like this, then you should normalize your data structure. That means, don't store lists in comma-delimited fields.

answered Apr 14 '14 at 19:12

Gordon Linoff

1,242,037
58
646
786

Wouldn't that cause a LOT bigger database? – Fibonacci Apr 16 '14 at 18:07
It is the right way to store this type of data, and databases are designed to handle these types of problems. – Gordon Linoff Apr 16 '14 at 20:53

score 0 · Answer 3 · answered Apr 14 '14 at 19:08

What you want is a graph database.

You can pick something ready-made for that purpose or try to map your graph to SQL database.

For example like this:

users table
sender-receiver table (both foreign keys to user)
item extension table (extends sender-receiver)

something like this:

CREATE TABLE user (varchar user_id, varchar email, etc...);
CREATE TABLE link (varchar sender, varchar recepient)
   CONSTRAINT FOREIGN KEY (sender) REFERENCES user (user_id)
   CONSTRAINT FOREIGN KEY (recepient) REFERENCES user (user_id);
CREATE TABLE aldbum (varchar sender, varchar recepient, album stuff)
   CONSTRAINT FOREIGN KEY (sender, receipient) REFERENCES link (sender, recepient);
-- repeat extension tables for files, messages

Optimization dilema

3 Answers3