[wp-hackers] Taxonomy Schema Proposal

Elias Torres elias at torrez.us
Wed Apr 25 05:17:21 GMT 2007

I implemented tags on Apache Roller not long ago and we decided not to
create a terms table. We only have a table for entry2tag and a table for
aggregates (tag clouds).

create table roller_weblogentrytag (
    id              varchar(48)   not null primary key,
    entryid         varchar(48)   not null,
    websiteid       varchar(48)   not null,
    userid	    varchar(48)   not null,
    name            varchar(255)  not null,
    time            $db.TIMESTAMP_SQL_TYPE 	not null

create table roller_weblogentrytagagg (
    id              varchar(48)   not null primary key,
    websiteid       varchar(48) ,
    name            varchar(255)  not null,
    total           integer		  not null,
    lastused        $db.TIMESTAMP_SQL_TYPE 	not null

Basically, we store for every entry, one row per tag. This eliminates
most joins. Additionally, we have an aggregate table per website that
keeps track of the total number of times a tag is used. Remember Roller
is the equivalent of WP-MU, not WP.

Anyways, just a thought that joins might not be the best way to go.


Mark Jaquith wrote:
> On Apr 18, 2007, at 5:04 PM, Matt Mullenweg wrote:
>> Ultimately speed is going to be a big factor in any implementation, I
>> hope that the 3 additional tables and related queries don't hit us too
>> hard on the performance side.
> If the API for updating/adding/removing tags and post categories is
> solid (i.e. we won't expect people to have a reason to circumvent it),
> we can cache cats/tags in postmeta whenever they are updated.  That
> would actually REMOVE a query from the existing system (as postmeta is
> already queried).  The system could be fail-safe, so that if there is no
> appropriate _wp_categories postmeta entry, it can do the big JOIN'd
> query (and at that moment, populate the postmeta cache).  That doesn't
> help us for Tag/Category views (which posts have X taxonomy?), but it
> helps us when querying tags/categories for a given set of posts.
> I'd assume that would be faster than doing a three-way JOIN'd query...
> it certainly sped UTW up.
> -- 
> Mark Jaquith
> http://markjaquith.com/
> Covered Web Services
> http://coveredwebservices.com/
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers

More information about the wp-hackers mailing list