CODEWITHSUNDEEP
mysqlsql
Fibonacci
Fibonacci

Reputation: 494

Optimization dilema

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.

  1. 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.

  2. Join the 3 tables. But from what I understand I cant use LIKE in a JOIN.

Any ideas?

Upvotes: 0

Views: 56

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

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.

Upvotes: 1

Brian Kendig
Brian Kendig

Reputation: 2541

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.

Upvotes: 1

Dima Tisnek
Dima Tisnek

Reputation: 11775

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

Upvotes: 0

Related Questions