3

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 – Neil McGuigan Feb 2 '16 at 19:27
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. – steady_daddy Feb 2 '16 at 16:18
3

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?

SELECT COUNT(*)
    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? – steady_daddy Feb 3 '16 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 '16 at 6:28
  • Right. I was leaning towards this approach too. – steady_daddy Feb 3 '16 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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