0

In a project in which I need to implement like/unlike functionality, I want to know which approach would better than other from performance point of view. If I have to go with insert delete, then what would happen if someone like/unlike it so many times (Then probably most of primary key numbers will be occupied based on like/unlike)?
Does anyone know how social networking sites do it?

EDIT:
My table is as below: 

CREATE TABLE `junc_user_share_like` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `jusid` int(10) unsigned NOT NULL,
 `liker` mediumint(8) unsigned NOT NULL,
 `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `jusid` (`jusid`,`liker`),
 KEY `liker` (`liker`),
 CONSTRAINT `junc_user_share_like_ibfk_1` FOREIGN KEY (`liker`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `junc_user_share_like_ibfk_2` FOREIGN KEY (`jusid`) REFERENCES `junc_user_share` (`jusid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

EDIT 2:
As you said with last updates this is what I've inferred: 

INSERT INTO likes SET jusid=$jusid, liker=$liker
UPDATE junc_user_share SET likes=likes+1 WHERE id=$id

This works great for likes, but what if someone wants to undo his like? Should I delete record from likes? My problem is exactly here? What to do here?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Alireza
  • 6,497
  • 13
  • 59
  • 132

1 Answers1

0

Denormalize.

  • have a table with the likes and unlikes (Who did what to which item)
  • have a likes and a unlikes field on the items table, both starting with 0

On a like/unlike do (assuming 1=like, 0=unlike)

INSERT INTO likes_unlikes SET item=[id of item], user=[id of user], action=[0|1];
UPDATE items SET likes=IF([action]=1,likes+1,likes), unlikes=IF([action]=0,unlikes+1,unlikes) WHERE id=[id of item]

A UNIQUE KEY on likes_unlikes(item,user) gives you uniqueness as a present.

Edit

After discussion, if you just have likes, but no dislikes, the situation is even easier:

  • only a likes table is needed with item and user id
  • only a likes field is needed on the items table

the queries are now something like 

INSERT INTO likes SET item=[id of item], user=[id of user];
UPDATE items SET likes=likes+1 WHERE id=[id of item]

and for reversal of a like:

DELETE FROM likes WHERE item=[id of item] AND user=[id of user];
UPDATE items SET likes=likes-1 WHERE id=[id of item]
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • I have table with likes and unlikes: who liked which post. – Alireza Jul 18 '12 at 08:01
  • So you have one part. Implement the second part and you are fine! – Eugen Rieck Jul 18 '12 at 08:04
  • In your approach you have to update two tables. Mine was one table. – Alireza Jul 18 '12 at 08:11
  • Yep, that's true - one more update for every like/unlike. On the other hand, if you **display** the likes/unlikes, you lose all the joins on the `SELECT`, which gives an enormous speedup, Since I am quite sure, that for every **written** like/unlike you will have hundreds or thousands **reads**, this is a good deal. – Eugen Rieck Jul 18 '12 at 08:14
  • I just show likes there is actually no unlike (unlike: undo your like). My approach is more like G+. – Alireza Jul 18 '12 at 08:18
  • This makes the situation even easier: drop the `action` and `unlikes` fields and all references to them. – Eugen Rieck Jul 18 '12 at 08:19
  • If it's possible please see the `EDIT 2` part of my question, thanks for your patience. – Alireza Jul 18 '12 at 08:30
  • Just reverse the statements: `DELETE FROM likes WHERE jusid=$jusid AND liker=$liker` and `UPDATE junc_user_share SET likes=likes-1 WHERE id=$id` – Eugen Rieck Jul 18 '12 at 10:14