[wp-hackers] Meta tables: Take 5

[Mike Schinkel]

On Aug 5, 2009, at 10:41 PM, Casey Bisson wrote:
> Assuming you've got a good index, that you can keep the table open  
> and indexes/keys cached, and that you're querying/joining against an  
> indexed column, the column type doesn't much matter.

Now there you go! I was all ready to let this die and you had to stir  
the pot! :-)

> A table with a million rows and six distinct string values for the  
> meta_type (or whatever) should have exactly six entries in the index  
> for that column. Querying the by meta_type starts at the index,  
> which lists all the matching row numbers.

I'm not 100% sure I'm following you here.  Are you saying that MySQL  
creates a hash for string values in an index when there is only a  
small number of values making string values more efficient than they  
would otherwise be?

If that's not what you meant, I'm curious to understand.  If it is  
what you meant, how do you know?  I see no evidence where (other than  
for in-memory tables): http://dev.mysql.com/doc/refman/5.0/en/create-index.html

And if MySQL does do that, when does it stop hashing distinct string  
values? Or does it ever stop?  Does it just create what is essentially  
a lookup table internally within the index (if it does, it's quite the  
optimization. And if it doesn't, I'm starting to wonder why not...)

> And, this discussion hasn't considered that the current post_meta  
> table isn't very useful as a tool for querying posts. That's what  
> the taxonomy tables are for. It's very difficult (some might say  
> foolish) to build a table that's optimized both for mass storage  
> (such as the serialized content that often appears in post_meta)  
> _and_ fast retrieval (such as identifying all the posts with a  
> matching taxonomy value). WordPress already has a good taxonomy  
> system that's well optimized for search and retrieval. And it's  
> extensible to all the data types WP now supports.

Are you saying that meta is not needed because of the taxonomy  
system?  If yes, that's ironic because some of what I'm doing lately  
really has me lamenting that limits of the taxonomy systems.

Meta is used for attributes and values. Name/value pairs in other  
words.  Taxonomy is used for classification.  But wait; what is  
classification? Is it not simply attributes with a value of "Yes",  
"No", or "Unknown?"

Of course it is.  Everything boils down to attributes as name/value  
pairs where the classification is also an attribute with potential  
values of Yes/No/Unknown.  I believe even Tim Bray now agrees[1].

So IMO the taxonomy system, while a good start, has a long way to go  
before it can fully usable to replace metadata.

> By the time that the database engine is looking at the meta_type,  
> it's already eliminated all but a couple/few rows, making arguments  
> about ENUM vs. VARCHAR rather pointless.

Back to this. Educate me please. Explain to me how the database engine  
has already eliminated all but a couple/few rows. Honestly, I hope to  
find out you have a point but I'm very skeptical.


-Mike Schinkel
WordPress Custom Plugins
http://mikeschinkel.com/custom-wordpress-plugins/

[1] http://www.tbray.org/ongoing/When/200x/2009/01/29/Name-Value-Pairs