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 gettingone post won't take 6+ seconds? =D
I understand you deal with premier platforms so you may have stumbled upon a similar issue?
@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
More specifically, it is "translation groups" rather than publishers. Different translation groups release different chapters from the same novel... if that makes sense.
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...
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