{"id":1613,"date":"2007-09-25T22:15:27","date_gmt":"2007-09-26T01:15:27","guid":{"rendered":"http:\/\/www.hoogervorst.ca\/arthur\/?p=1613"},"modified":"2007-10-19T10:38:45","modified_gmt":"2007-10-19T13:38:45","slug":"me-count","status":"publish","type":"post","link":"http:\/\/www.hoogervorst.ca\/arthur\/?p=1613","title":{"rendered":"Me count(*)"},"content":{"rendered":"

I<\/span> just finished upgrading to WordPress 2.3: so, curious as any developer would be, I took a look in the WordPress database definitions and noticed that three new tables were added. All of them take care of categories and (the new) tagging system. This means, Wp_Cat is out and has been replaced with wp_terms: the actual distinction (i.e., which terms is a category and which one is a tag) is now made in the the wp_term_taxonomy table.\n<\/p>\n

I have issues with that last table because it has a count column (to track the number of posts). This is the second table that has a count column (earlier<\/a>): I mentioned before that version 2.0 introduced a comment_count in the wp_posts table. Why not make use of the regular aggregate functions (like the standard COUNT(*))? After all, these aggregates are generally highly optimized functions (written in C) for tables with the same primary key(s). Also, as a good database designer, during database design you should take the use of aggregate functions in account when setting up your tables structure.\n<\/p>\n

Notice that count of posts for a tag\/category can be done simply by:\n<\/p>\n

select count(*),
\n p.term_taxonomy_id,
\n q.taxonomy,
\n t.name
\nfrom wp_term_relationships p,
\n wp_term_taxonomy q,
\n wp_terms t
\nwhere p.term_taxonomy_id
\n = q.term_taxonomy_id
\nand q.term_id = t.term_id
\ngroup by p.term_taxonomy_id,
\n q.taxonomy, t.name
\n<\/code><\/p>\n

Sure: a query on that same table with that count column may look as easy as ‘select count from wp_term_taxonomy’, but remember, it (always) takes an extra write to put a number in that table (via a regular UPDATE). If your transaction on that table ever failed, your fancy report will most likely report wrong numbers.<\/p>\n","protected":false},"excerpt":{"rendered":"

I just finished upgrading to WordPress 2.3: so, curious as any developer would be, I took a look in the WordPress database definitions and noticed that three new tables were added. All of them take care of categories and (the … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[15,16],"tags":[45,576,577],"_links":{"self":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/1613"}],"collection":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1613"}],"version-history":[{"count":0,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/1613\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1613"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1613"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1613"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}