0

For each user the following is to be saved:

  • user_id
    • tag / tag_id (eg. 1,2,3,4,...)
      • timestamp first_seen (for each tag)
      • timestamp last_seen (for each tag)

Options which come to my mind:
  1. One Row per User
    One row per user containing the meta_key (eg. "user_tags_data") and save the data to the meta_value field.
    The tags and the associated timestamps are saved serialized. So this would result in (multiple tag + timestamps) combinations all saved in one serialized value


  2. Multiple Rows per User
    For each user there will be multiple rows (depending on the number of tags assigned to the user). For each assigned tag there will be a row with a dedicated meta_key (eg. "user_tag_1_data") and the data is saved to the meta_value field.
    The timestamps are saved serialized.


  3. Separate Table
    Or create a separate / dedicated table within the database to save the data and don´t use the WP tables at all.


--- edited / begin ---

Use of the data:
User Content Restriction
The data is used to check if a (logged in) user has the appropriate tags in combination with the (first_seen / last_seen) timestamp to see if it´s before or after a post's published date. Depending on these value will result in the user to have access or to not have access to that post he´s trying to open/view.

Currently there are about 10k users logging in on a weekly basis, mostly after a new piece of content is published and the users are informed by a notification / email.
A % of the Users will therefore login and try to open / visit the new published posts at the same time, so concurrent users. Although the notifications / emails are only send to those with the correct access rights (tags & timestamps), it still needs to be verified when a user tries to open/visit the post of course.

The list is growing quickly and is expected to be 20-40k by the end of the year.

User Dashboard, List(s) of Posts - CAN / CAN NOT access
Show the user - when logged in - a list of the posts he HAS access to and a list of the posts he does NOT HAVE access to.
The list shown in the dashboard will probably be limited to 5-25 posts (sorted by date / randomly), but there will be a link to load all (accessible / not accessible) posts in a new page or lightbox/modal.

--- edited / end ---

What is considered best practice when it comes to saving data (consisting of multiple values)?

I am asking in regards to performance / speed too, because with a lot of users it could resolve in a lot of data.

E.g. 25k users with each an average of 20 tags could result in 500k rows, which could / would impact the database query performance, I guess.

I look forward to hearing from you all!
(disclaimer, I am a beginner...)

8
  • 2
    Can you phrase this in a way that makes it clear what the specific question is soo that a specific answer can be written? Remember, this is not a discussion forum, you need to write a question in such a way that someone can write a solution using facts that you then mark as not just the best answer, but the answer, the answer all people with this question will see and agree that it is the answer for all people with this question
    – Tom J Nowell
    Jul 29, 2021 at 16:17
  • 2
    Also, a heavy dose of "it depends" is needed here, you haven't told us much about what you're going to do with this data and how you intend too query or display it which will have a huge impact on the viability of some of the options. E.g. if you intend to list users who saw something within a time period, then that's significant and fundamentally changes the question
    – Tom J Nowell
    Jul 29, 2021 at 16:19
  • 1
    Will you need to query by these values? Ever? If so, you need separate rows.
    – vancoder
    Jul 29, 2021 at 19:03
  • @TomJNowell Thanks for your comment. I have edited my question to make it clearer what I would use the data for. Hope that helps. Thanks
    – Elv1s
    Jul 30, 2021 at 8:27
  • @vancoder Thanks! Yes, I will need to. Do you think it´s best to have separate rows in the existing usermeta table or to create and use a dedicated table for it? ps. I also updated / edited my question to hopefully make it clearer what I am trying to achieve. Thanks.
    – Elv1s
    Jul 30, 2021 at 8:41

1 Answer 1

0

This is written on the assumption that this data will only be retrieved when the user ID is known, and always in full for that user. If any kind of filtering is needed, if you need to filter users themselves by this data, then this answer will no longer apply.

One Row per User

This works but only if you fetch the entire data and process it in PHP. Serialized data in the database is a bad idea.

Multiple Rows per User

This will be just as fast as the one row per user, with the advantage of it being a nicer query, and the data is easier to handle. Always use this option over the previous option. Serialised data is not a good idea.

Separate Table

This will be just as fast as the previous 3 options

Large Row Count Anxiety

I imagine you've used sites that rely on post meta queries, and noticed that as they get bigger, they get slower. This isn't because having lots of rows in a table is slower. Databases are designed in a way that can handle millions of rows, the performance slow down of extra rows is not enough to explain your problems.

Instead, the queries are the problem. Some queries are fast! E.g. finding a meta value when you already know the key and user ID is blazingly faster. Partially because WP fetches a user or posts meta values in bulk to save time and stores it in WP_Cache ( which is also why object caches make such a big difference ), but mainly because those tables were designed for those queries, and have indexes. The database doesn't scan each row of a table when you do a query, that's something a database does as a last resort in a worst case scenario.

By making bad data storage decisions, and running queries tables weren't designed for, that's when large tables get slower queries.

So on a site with 10 users, that query to find all users with a meta that has the value ABC will be quick, as will get_user_meta( 1, 'xyz' true )'. But on a site with 1 million users, get_user_meta will be a little slower, but that query to find all users with the meta of ABC will grind to a halt.

NOT/exclusions

User Dashboard, List(s) of Posts - CAN / CAN NOT access

Likewise when you ask not style queries. By doing this the database has to evaluate each row by row to test if it is or it is not, building a brand new temporary table in memory. Once it's done this in memory copy, then it runs the query on the new table, before throwing it away. That's why NOT IN OR __not_in parameters are so expensive. It's also why the RAND sorting is so expensive. Not only that, but they dramatically hurt scaling. Your server only has so much memory to go round, if it's full of temporary tables executing these exclusion queries then only so many people can access the site at the same time, PHP workers will have to wait their turn.

3
  • 1
    Thanks a lot for taking the time to write such a detailed answer. I really helps me understand it better. Much appreciated.
    – Elv1s
    Aug 3, 2021 at 12:17
  • If it helps, the best way to avoid NOT style queries but keep that functionality is to add inverses. E.g. don't add a hide_on_homepage term then use NOT, instead add a show_on_homepage term, add it to every post on save that doesn't have hide_on_homepage, then query for that instead, turning a super heavy/slow query into a very fast query. You can have your cake and eat it, you just need to be smart about what you store and query for. Databases can store millions of rows just fine, it's the querying that's expensive! ( Unless hard drives are unusually expensive )
    – Tom J Nowell
    Aug 3, 2021 at 14:10
  • Thanks, that surely helps! Much appreciated.
    – Elv1s
    Aug 5, 2021 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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