[wp-hackers] Creating Custom Taxonomies on the fly
Dion Hulse (dd32)
wordpress at dd32.id.au
Sun Jan 30 06:35:39 UTC 2011
I'll be honest, I'm not a SQL performance nut, and I hate data duplication.
I've just grabbed a SQL in use on a reasonably -low traffic- site which
has about 200 terms in total and a few hundred posts, the result of this
is transient cached(which is cleared when tags/cats change) and the site
uses super cache as well, So the site has never had any performance issues
or scaling problems due to this - Thats not to say it won't in the future
SELECT DISTINCT t.*
FROM wp_terms AS t
JOIN wp_term_taxonomy AS tt ON ( t.term_id = tt.term_id AND tt.count >0 )
LEFT JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id =
WHERE tt.taxonomy = 'post_tag'
FROM wp_term_relationships AS tr
LEFT JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id =
WHERE tt.term_id = 3 # This is the Term ID of the category (or any
related taxonomy really) that you want to intersect the tags with
ORDER BY t.name ASC
I'm sure it's not the best query, it could certainly be optimized (I'm
pretty sure it's possible to get rid of that sub select, that'll choke
when there are hundeds of posts in a category), but my main reason for
digging it up, is the point out that you don't have to load all the posts
up, just the ID's of the posts, which is stored in the taxonomy tables
Ultimately, The correct way of doing things, is what ever is the easiest
for the developer, and which they can make scale well.
On Sun, 30 Jan 2011 13:45:51 +1100, Haluk Karamete
<halukkaramete at gmail.com> wrote:
> Dion, here is the part I don't get; "We want Tags which are attached
> to <list of posts in category>"
> I think your solution approaches the matter from a different
> implementation. My first idea was to sort this matter our per post and
> at post update time. One post at a time. From you, what I get is do
> this every 24 hrs or so as a batch process. That for sure will cause
> some DB crunching while that process is sorting the matter out.
> Let's take it from another example... that is run time processing at
> categorty.php level and relying on super cache magic.
> category.php will ask
> what category of posts I'm supposed to deliver now?
> answer: deliver catid:14
> category.php will ask to wp_posts and to wp_term_relationships and to
> wp_terms tables
> Give me a list of all the distinct tags that belong to those posts
> where the posts have been categorized under 14.
> This is one heck of SQL query. I would never do it.
> I think doing this at post update time is better. What do you think?
> On Sat, Jan 29, 2011 at 6:30 PM, Haluk Karamete
> <halukkaramete at gmail.com> wrote:
>> For the site I have in mind, 100's of cats, 1000's of posts... So
>> whatever it is got to scale up.
>> On Sat, Jan 29, 2011 at 3:43 PM, Dion Hulse (dd32)
>> <wordpress at dd32.id.au> wrote:
>>> On Sun, 30 Jan 2011 10:33:50 +1100, Haluk Karamete
>>> <halukkaramete at gmail.com>
>>>> Do you see some value in this approach? Or are there easier/smarter
>>>> ways to address the problem I'm trying to solve with less hassle?
>>> How many posts are we talking here?
>>> Whilst your proposed selection would work, it does sound like a lot of
>>> processing to ensure that the terms are kept in sync..
>>> My method would be:
>>> Generate tag cloud
>>> - Are we on a category view?
>>> - No: Generate as normal
>>> - Yes: Filter the terms:
>>> - We want Tags which are attached to <list of posts in category>
>>> You'd have to add some caching to that, and theres a good chance you'd
>>> to filter some SQL there for best performance.
>>> I've done something similar (intersections of terms) for navigational
>>> elements of a custom taxonomy, ie. Site has multiple sections, and a
>>> set of
>>> navigational elements to drill down, In the drill down, you only want
>>> elements selectable which the section of the site has available
>>> Dion Hulse / dd32
>>> e: contact at dd32.id.au
>>> Web: http://dd32.id.au/
>>> wp-hackers mailing list
>>> wp-hackers at lists.automattic.com
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
Dion Hulse / dd32
e: contact at dd32.id.au
More information about the wp-hackers