[wp-hackers] Metadata Mess

[Mike Schinkel]

On Dec 22, 2009, at 4:08 PM, Otto wrote:
> On Tue, Dec 22, 2009 at 2:42 PM, Malaiac <malaiac at gmail.com> wrote:
>> See, you are focused on portability and one time performance of...
> 
> Almost forgot, the "ALTER" is not a one time thing. If you use enums,
> then every time you add a new meta type, your table gets altered.
> ENUM's are enumerated, meaning that they're pre-defined. You can't add
> new ones on the fly, which is sorta the whole point behind meta.

I think you are viewing "one time" too literally.  Clearly the frequency with which ENUMs would need to be modified are at least two orders magnitude less than the number of times a query is called to load a set of posts, wouldn't you agree?

> That was what he was talking about. Use of ENUMs is a major
> performance drain for most sites (you know, the ones which aren't
> using 30 metas on each post and 20k comments on every page).

Now to be clear, I don't think anyone is arguing that meta_key should be an ENUM (or are they?) but instead for use of an ENUM to enumerate post types in a single meta table. The number of times you'd need to add a new post type would be small indeed.

> On Tue, Dec 22, 2009 at 2:42 PM, Malaiac <malaiac at gmail.com> wrote:
>> See, you are focused on portability and one time performance of an
>> ALTER table. I'm focused on long term performance of heavily used meta
>> tables. No way I'm gonna suffer a VARCHAR search for each and every
>> meta. There are sites with more than 20 000 comments on a single page.
>> There are sites with more than 30 meta values per post. ...
> 
> And those sites are not the norm, and so should be using customized
> solutions to solve their specific needs.

The problem with that argument is that most sites with 20,000 comments per post started as a brand new WordPress blog.  In an ideal world you are correct, but in the real world those types of site evolve to be from what you call "the norm."

> If a site has 30 meta values per post and is using them extensively,
> then they are doing it wrong.

"Doing it wrong?"  LOLcats, right? :-)

> They should be using some form of plugin
> with a custom table to handle their needs.

That's a really good point to discuss; *should* plugins be creating custom tables that are unaccessible to any other plugins that don't have pre-knowledge of that plugin, or would it be better to have a shared architecture of tables for those needs?  I'd argue that's where the custom table architecture I proposed makes uber-sense.

Personally plugins that create custom tables are (one of) the bane(s) of my WordPress existence.

> If a site has 20000 comments on a single page, then they are doing it
> wrong. They should be using some kind of forum software, or perhaps
> "paging" of their comments.

"Doing it wrong" again?  Think of how many got to the point of having 20,000 comments.  By starting a site that eventually grew to be popular.  

BTW, I interpreted "one a single page" to mean associated with a single "wp_posts" record, not how it was displayed.

> WordPress should not be developing core code to fill the needs of the
> tip of the long tail.

That's (another) place where we disagree.  It should not handle it in its default state but should IMO be architected to be able to scale to that level when someone reaches success without them having to hire a programming team to implement scalability on a per site basis just to support their successful personal site.  

BTW, did you see the request on wp-pro today for someone to help? Here is part of her request for help (note the traffic of 10k-13k pages and 70% of a 8 gig shared server):

Moderately busy Blog has run successfully for 2 years, Originally installed via Fantastico, Accessible via CPanel11, MyPhpAdmin, MSQL 5 (17 gig database), PHP Ver 5.29 Version.  The blog is crippled right now but on a nomal day I get about 10,000 - 13,000 page hits per day, 4000-6000 unique visitors,  Host claims I'm using up to 70% of a 8 gig shared server.  They also report that each process they allow is currently using about 80 mb of memory.  Currently on version 2.7.1 with no desire to move to current version yet.  Running 10 plugins including WP-Super Cache.

Doesn't sounds like that much traffic and this woman didn't sound like she ever expected to get to that point.  Hmm?

-Mike
P.S. BTW Otto, you going to come down from Tennessee to WordCamp Atlanta Jan 8-10?  Would love to finally meet face-to-face.