[wp-hackers] Meta tables: Take 5
mikeschinkel at newclarity.net
Fri Aug 7 03:41:15 UTC 2009
On Aug 6, 2009, at 3:54 PM, Otto wrote:
> On Thu, Aug 6, 2009 at 12:12 AM, Mike
> Schinkel<mikeschinkel at newclarity.net> wrote:
>> 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?
> No, but if you only have a small number of values, then your b-tree
> (or whatever structure it's using) never grows larger than that number
> of values. This is irrelevant to the size of the index field (except
> so far as you'd like all the values to fit into memory).
Forgive me, but I do not at all understand how what you are saying can
be true unless we are talking apples & oranges. Let me state an
example to see if we are saying the same thing.
Assume we have a 1 million records table called wp_metadata. It has
fields meta_id, object_id, meta_type, meta_name and meta_value. The
field meta_type is VARCHAR(20) has one of four values: "post", "page",
"comment" or "link" and you have a key based on meta_type+object_id.
Everything I know about indexes (though am willing to learn otherwise)
tells me that that you'll need to store 1 million values for meta_type
+object_id in order to index those tables and not just 4. How is it
you think you can have an index that can represent 1 million records
in the index without their key values? What you are saying makes no
sense to me (unless they are hashing the values internally.)
> To put it another way, if you only have seven unique items, then your
> b-tree is only 3 levels deep, no matter how many characters your
> column contains. So finding the rows is easy, there's no long
> searching or anything else.
How you can have a b-tree with only 3 levels deep with a 1 million
records? 10 million records? 100 million records? I don't see how
it is possible (I hope I'm wrong, so please educate me.)
WordPress Custom Plugins
More information about the wp-hackers