[wp-hackers] Restructuring Category Hierarchy

Jamie Talbot wphackers at jamietalbot.com
Fri May 4 22:20:36 GMT 2007

Hash: SHA1

As part of the taxonomy improvements that are due for 2.3, wp_categories and wp_post2cat are likely
to be removed or significantly altered, no matter what schema we end up going for (the current
proposal is at trac ticket #4189[1]).  Our current category schema is more optimised for inserts
than selects.  For subcategories for example, we either have to cache the entire category table into
a complex array, or recursively retrieve data.

A better model, more suited to WordPress ideals, is the other way round, where we take a _slight_
hit on inserts, so that we can instead select whole subsets of data very quickly.  This can be
achieved by using the Nested Set Model, described on MySQL's website[2].  That page has lots of
optimised queries for common usage which model our needs very well.  The vast majority are 4.0
compatible, with a few less common examples requiring 4.1.  Given that there is going to be a
sizeable structural shift anyway, I'd like to see us move to this new model, which should offer big
speed improvements.  API for categories will already have to change to fit the new schema, so we
should change it all in one go.

The changes basically boil down to replacing the single 'parent' column with two 'left' and 'right'
('lft', 'rght') columns.  For tagging, these columns aren't so useful, but they are for
post-category and link-category, and I can imagine other taxonomies that would also be made
possible, like linked-list.

I raised this on the ticket above, but there haven't been any responses to it there, so I'm
wondering what the hackers think?  If we miss this opportunity for change, it will be a long time
before we have another chance.



[1] http://trac.wordpress.org/ticket/4189
[2] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

- --
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org


More information about the wp-hackers mailing list