[wp-hackers] Meta tables: Take 5

[Mike Schinkel]

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.)

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