Building a scalable WordPress favouriting plugin – one serialised meta value array or many meta records

You forgot option 3 – Add a special table in which the pair (user,post id) will be the index. Ok I am not a MySQL person so maybe it is too extreme, but maybe having two tables one with users as index and one with posts will be even better.

The thing about performance is that there are rarely absolute solutions for everybody at anytime, and the “best” depends on your actual usage pattern, not the theoretical one. Or in other words, you are doing early optimization here.

While option 2 seems to be faster for this specific information it will make the meta tables bigger and therefor is likely to slow down all requests to those tables (therefor the suggestion for option 3 which is very similar but do not impact other queries).

Another issue you ignore here is the cost of data insert/update. This operation is much slower then a read and can not be cached. If you are going to have many likes at the same time, with option 2 you will lock the tables and requests will need to wait for others to complete and each insert will be slower while option 1 is likely to end in data corruption under naive implementation (two changes in the same time, at best only one of them will have impact).

And then you should take into account what kind of caching will you do. With a good caching scheme the read time is a non issue.

To conclude I wish that you will find this to be an actual problem that needs to be solved, till then just write proper api to access/change that data to hide the implementation detail so if it will become a problem you will be able to change the implementation without impacting the rest of your code.

Leave a Comment