[wp-hackers] Meta tables: Take 5

[Mike Schinkel]

> On Thu, Aug 6, 2009 at 10:41 PM, Mike
> Schinkel<mikeschinkel at newclarity.net> wrote:
>> 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...
>
> Why would you make the index meta_type + object_id? I was talking
> about just an index on the type. I'd want an entirely separate index
> on the object_id, since you're going to probably pull records by
> object_id a lot.

meta_type + object_id uniquely identifies a record because you can  
have an object_id=1 for a post and an object_id=1 for a page, etc.

Of course, I'm now seeing your point which is that if you only index  
on object_id and there are never more than a few records with a given  
object id then selection of record by meta_type can be more  
efficiently handled in memory vs. having an index on it.  Gotcha.  
Thanks.

>> 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.)
> The number of records is irrelevant to the index depth (its size grows
> as a function of number of records). The depth of the b-tree is
> determined solely by the number of unique values that you're  
> indexing on.

However that still does not sounds correct based on everything my  
computer science profs taught me about B-trees. OTOH, it's a moot  
point related to the topic of this discussion so feel free to drop this.

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