Limit number of mysql records with a certain attribute

Question

Help, please, with an efficient way to enforce a limit on the number of books a user marks as favorites.

user_book table

bookID | userID | Attr | addedTime  <- with index UNIQUE(bookID,userID)

The Attr column holds bitflags of attributes the user sets on a book. For eg:

• 1 (binary 001) if user owns the book,
• 2 (binary 010) if user read it,
• 4 (binary 100) if the user marked it as favorite

So Attr=6 means the user does not own the book, but she read it and marked it as favorite

When a user tries to set the favorite flag on an existing record or add a new record with that flag set, I'd like to enforce a 25 favorites limit. If the limit has already been reached, I'd like to remove the flag from the oldest favorited record. My current implementation seems overly complex:

Suppose user 25 wants to set book 100 as favorite, I would do (pseudocode)

//sql to get all books the user has marked as favorite; oldest book first
SELECT bookID from user_book WHERE userID=25 AND (Attr & 4)>0 
    ORDER BY addedTime ASC 

if(($count = count($results)) > 24){ //if more than 24 books are favorited
    $bookIDsToUnset = array_slice($results,0,$count-24); //get books over limit
    $bookIDsToUnset = implode(',' , $bookIDsToUnset);

    //unset the favorite attribute from the books over the limit
    UPDATE user_book SET Attr = Attr^4 WHERE userID=25 
        AND bookID IN ($bookIDsToUnset)
}

//make bookID 100 a favorite
UPDATE user_book SET Attr = Attr | 4 WHERE userID=25 AND bookID=100

This requires up to 3 DB queries, so it seems inefficient. Can anyone suggest a better alternative?

Answer 1

You can unset the favorites in a single query:

update user_book ub join
       (select ub2.*
        from user_book ub2
        where ub2.userId = 25 and (attr & 4) > 0
        order by ub2.addedTime desc
        offset 24 limit 999
       ) keep24
       on ub.bookid = keep24.bookid and ub.userid = keep24.userid
    set attr = attr ^ 4;

With an index on user_book(userId, addTime) and user_book(userId, bookId), this should be pretty fast.

You can then insert the new favorite using your insert statement.

Note: I don't think it is a good idea to automatically remove favorites, but that seems to be your application design.