[wp-hackers] Meta tables: Take 5
mikeschinkel at newclarity.net
Thu Aug 6 05:12:11 UTC 2009
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.
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.
WordPress Custom Plugins
More information about the wp-hackers