@JoeTaxpayer This question on SO might be helpful

Hey guys, what was the link for browsing wp plugin repository codes? Could you please provide me the link..

@JackJohansson - Thanks! That answers both questions, the speed issue, and where these things are discussed.

@TomJNowell Tom, could I borrow your code review consciousness for a moment. I have this tax_query:

array(
'numberposts' => 1,
'post_type' => 'cc_group',
'post_status' => 'publish',
'ignore_sticky_posts' => true,
'no_found_rows' => true,
'tax_query' => array(
    array(
        'taxonomy' => 'cc_release',
        'field' => 'term_id',
        'terms' => $release_term_id
    )));

There are over 250,000 cc_release terms and $release_term_id is attached to a post that has 40,000 cc_release terms attached to it. When I run this query, it takes around 6 full seconds. If $release_term_id is attached to a post that only has a few terms, then the query is ultra quick.

Is there a way to actually improve this query... so getting one post won't take 6+ seconds? =D

I understand you deal with premier platforms so you may have stumbled upon a similar issue?

Are numberposts and posts_per_page interchangeable?

@ChristineCooper true, but usually those kinds of data structures are a red flag, also +1 to the numberposts and posts_per_page, I'd stick to the first

what does cc_release mean?

and what is a cc_group? There's likely methods of reducing the sheer volume of stuff, or segmenting it further semantically

it could be that at this point, the limiting factor is the database server

be it memory thrashing, or disk speed

you can add the post author, but I don't think that would yield much performance gain

cc_release is "chapters" of novels. cc_group is the publisher. So my option is to store the target post id in the term custom meta.

hmmm why would you need to attach chapters directly to the publisher group then?

I understand it makes it easy to fetch which publisher the book is from

but it's more efficient to fetch the novel post and then figure out the publisher from there in a 2 step process

More specifically, it is "translation groups" rather than publishers. Different translation groups release different chapters from the same novel... if that makes sense.

hmm

so lay out the data relations in your mind as a node map

i suspect, that every node is connected?

in which case, that might not make the most sense here

so your goal is to be able to list all the things that a translation group is working on or has worked on?

The setup: the chapter (term), is connected with 2 posts. One is a novel, the other one is a translation group.

where a deliverable is a chapter for example

Yes, to some degree. Basically, I display the chapter (and related term datas), and which translation group that released it. The problem is when I am fetching the translation group (the query above).

I sense that the most viable solution is to store the translation group's id in the chapter's term meta...

so novel and translation group are connected via a chapter term

what's the maximum number of chapters you can expect in a novel?

And simply do a get_term_meta($id, 'cc_group_id'. true)...

The biggest novel so far has around 4000 chapters, and rising..

Yes, they are connected via the chapter term.

that works but a querying taxonomy terms via their meta is just as expensive as querying post IDs via their meta

what if you flipped it around?

so that novels have chapters

and groups have novels

but yeah you could store the ID of the group in term meta

as long as you have hooks to update it, and you only ever use get_term_meta to retrieve it

Meaning, with your approach, novels are cpt, and chapters/groups are tax.

if you wanted to be really crazy, and I don't recommend it, you could use a taxonomy taxonomy

eh something like that

What do you mean with taxonomy taxonomy? =D

eitherway it's a lot of terms

Aha I get you.

well, you know how a taxonomy has terms that group object IDs?

what's to stop those objects themselves being term IDs

Yup.

granted, the UI would be strange, and it has very little uses

It's too bizarre haha

I think I will go with storing the post id within the term meta and fetching it via get_term_meta.

The joys of optimization.

Thanks for the feedback.

Out of curiosity, do you often stumble upon issues like these with the bigger platforms? I presume most of them were designed with these scenarios in mind.

I can't think of one with posts that have thousands of terms

in these situations, I'd consider using custom tables and an ORM library

or flattening things a little

it's a little less about how semantically things fit together

and more about what kind of queries you want to make and figuring out the data relationships accordingly

so it could be that chapters become a CPT and you use post_parent

as that's an indexed value, but it constrains you

as well as figuring out which direction relationships should go

so in this case you need to figure out a publisher/translator via the chapter

ideally you'd design things so that either that was easy to query, or that situation is never there because you already have that info from the context