MySQL: Soft Delete post likes

Question

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?

Answer 1

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.

Answer 2

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.