I have implemented a like/unlike features for posts in one of my projects. The like table is normalized and has a column visible. The schema is as follows:

Column      Type       Null    Default  Links to
id          int(11)     No           
user_id     int(11)     Yes     NULL    user  -> id      
post_id     int(11)     Yes     NULL    posts -> id      
visible     tinyint(1)  Yes     NULL         
created     datetime    No           
updated     datetime    No 

What I'm doing currently is that when a user likes a post, I create a record and when he un-likes it, I just set the visible column to false. The count is calculated by post_id and visible = true. I did that to prevent excessive load on database when a user likes/unlikes a post.

My question is am I being paranoid in considering database performance so early (without much traffic) or is this among the best practices and I should continue with this?

  • 1
    Copy-on-delete to table_history Feb 2, 2016 at 19:27

2 Answers 2


You should consider the balance between how often your data will be accessed vs how often your data will be updated.

Adding another condition on your SELECT just to display your content may well create more load than deleting rows in the infrequent occasion that a user un-likes. Note that you will need an index on visible to efficiently query against it.

  • Thank you. That's a very good point made regarding index on visible field. Feb 2, 2016 at 16:18

If (post_id, user_id) is unique, then jettison the id and make that pair the PRIMARY KEY.

I specifically suggest that order because I assume this query is frequently used?

    FROM tbl
    WHERE post_id = ?
      AND visible;

By having the PK start with post_id, you get the added efficiency of "clustering", thereby drastically reducing the I/O when this table eventually becomes so large that it can no longer be cached.

I chose not to include visible in any way because (1) it confuses the "uniqueness" requirement of the PK, and (2) the row would have to 'move' when visible changes. This would be a bunch of overhead. (OK, maybe that action is rare enough not to care.)

See also my indexing cookbook.

If you want to discuss this further, please provide SHOW CREATE TABLE and the critical queries that hit the table.

  • Thanks Rick. I stumbled upon your cookbook earlier too and it very nicely explained the usage of index. There are currently indeces on post_id, user_id and visible columns. However, my question here is like asking whether I should hard delete the record when a user un-likes the post or just mark it as visible=false. Assuming he may want to like it again, I just have to modify a record. Does this make it more clear? Feb 3, 2016 at 5:04
  • 1
    Let's look at frequency. I suspect 97% of traffic is initial "likes", 2% is "unlikes", and 1% is "relikes". For efficiency, optimize the "likes" and don't worry about the other cases. For that, you may as well delete the row on an "unlike".
    – Rick James
    Feb 3, 2016 at 6:28
  • Right. I was leaning towards this approach too. Feb 3, 2016 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.