So, we've talked about this one but all the time up to now I've been putting off implementing it because I was never thoroughly sure how to efficiently handle doing likes.
The idea of doing a big (and potentially expensive) query on thread display never appealed to me but after sitting down and examining XenForo (which does it as a core feature) and seeing how they did it, I find myself unable to find a better way of doing it.
Specifically, their method is to embed an array of entries into the posts table, in a blob[1] and pull that out as display time.
Oddly enough, that actually contains the username as well so there's no extra performance hit there, but I imagine the change of display name is expensive if that person did a lot of likes.
So, for the programmers out there, what do you reckon?
As I see it, there are four options:
1. Just store the likes/post relationship and query it at display time (optionally with caching). Smallest in the DB, no additional maintenance, but it does mean there's an extra query - and we do have to run through that query in its entirety, it's not like we can stick a limit in, because you can't do a limit per criteria: you can't get "3 rows of that criteria, 10 rows of that", so we have to evaluate all the likes on the page at once.
2. Store the list of ids who like a post into the post table, and add that list of people into the main loadMemberData call (or do a second, smaller one just for minimal since we only need the names at that point). Much quicker and more efficient, but there's still extra work being done to handle the overhead of gathering names etc. (though of course we only get the right number of names, the first 3 or so actual names and just bundle the rest together)
3. Store the list of ids and names in the post table. Consumes a lot more space than either of the other two options, however it means there's no extra effort involved other than parsing the extra item (which need only be an unserialize call, or even a json_decode call, bearing in mind that we only need the first 3 or so rows, and can discard the rest after), and of course adds to the maintenance work if a user changes their display name.
4. We store just the count of likes in the post table, and fetch the list (using the same table as in 1) AJAXively if someone asks. Much lighter than the others, but far less nice IMHO.
Thoughts?
The idea of doing a big (and potentially expensive) query on thread display never appealed to me but after sitting down and examining XenForo (which does it as a core feature) and seeing how they did it, I find myself unable to find a better way of doing it.
Specifically, their method is to embed an array of entries into the posts table, in a blob[1] and pull that out as display time.
Oddly enough, that actually contains the username as well so there's no extra performance hit there, but I imagine the change of display name is expensive if that person did a lot of likes.
So, for the programmers out there, what do you reckon?
As I see it, there are four options:
1. Just store the likes/post relationship and query it at display time (optionally with caching). Smallest in the DB, no additional maintenance, but it does mean there's an extra query - and we do have to run through that query in its entirety, it's not like we can stick a limit in, because you can't do a limit per criteria: you can't get "3 rows of that criteria, 10 rows of that", so we have to evaluate all the likes on the page at once.
2. Store the list of ids who like a post into the post table, and add that list of people into the main loadMemberData call (or do a second, smaller one just for minimal since we only need the names at that point). Much quicker and more efficient, but there's still extra work being done to handle the overhead of gathering names etc. (though of course we only get the right number of names, the first 3 or so actual names and just bundle the rest together)
3. Store the list of ids and names in the post table. Consumes a lot more space than either of the other two options, however it means there's no extra effort involved other than parsing the extra item (which need only be an unserialize call, or even a json_decode call, bearing in mind that we only need the first 3 or so rows, and can discard the rest after), and of course adds to the maintenance work if a user changes their display name.
4. We store just the count of likes in the post table, and fetch the list (using the same table as in 1) AJAXively if someone asks. Much lighter than the others, but far less nice IMHO.
Thoughts?
| 1. | Eh, could be a text, can't think of any overwhelming differences, other than that it's harder to get a blob in a table, you have to prance around doing CAST() to get at it. |
