[wp-hackers] Like Posts - Store Data in DB, User_Meta or Post_Meta

[Otto]

On Mon, Nov 11, 2013 at 12:58 PM, Gregory Lancaster <
greglancaster71 at gmail.com> wrote:

> So I want to place this into a custom table instead, and if I am to do
> that, to minimize the sql load I wont be using arrays.  Rather the better
> option would be to create a new row for each like, with one col for the
> user_id that liked the post, another col for the post_id that was liked,
> and a last col for for if the person who clicked like was logged in or not
> to show how many anons or regular visitors liked the post.
>
> That means everytime a post is loaded, a database call will be made to
> query the table, grab all user_ids of for that post_id and run them through
> a get_userinfo() or something right?   That seems resource intensive.




No more so than using meta, it's just that meta is more hidden from you.

Take the meta case:
- Query posts
- display post
- see that it has meta (pre-loaded for you by a normal WP_Query)
- Call get users to get the users and display them, or whatever

That query posts gets not only the posts, but all meta associated with
those posts. That's two queries, at least. Getting the users is your third
query.

Same case with a custom table:
- Get the posts (we don't care about meta here)
- get the custom table data
- get the users

Again, three queries that directly concern us.

The problem isn't really with the number of SQL queries, it's in making
those queries inexpensive.
- In both cases you get posts. Same deal.
- Getting meta for posts is actually kind of cheap, because it simply gets
all the meta data for all the loaded posts, so it's querying on the
post_id, which is indexed.
- Getting from your custom table is basically identical to that, because
you can put an index the post_id column. However, much less data to sort
through in this case, because it doesn't have all the other meta info
- Then in either case, you get the users. Same deal again.

Meta has a slight advantage in that it uses the WP_Cache system by default.
Lets say you run a WP_Query that gets a whole bunch of posts. The meta for
those is loaded and cached. Later, you run a query that also gets some of
those same posts. Because the meta is already cached, it doesn't go fetch
that data again. Time saved right there.

However, a custom table storing only ID numbers relating to other ID
numbers will be very small by comparison to meta, which has large text
fields in it. MySQL can optimize much better with small tables that connect
numbers to other numbers. Queries on that will be super quick. And if you
really want, you can implement caching using WP_Cache yourself, with
wp_cache_set and wp_cache_get. If it's needed, which it probably won't be
at first.

-Otto