[wp-hackers] Meta tables: Take 5

[Otto]

On Thu, Aug 6, 2009 at 2:54 PM, Otto<otto at ottodestruct.com> 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).
>
> Fulltext indexes are different than normal indexes, mind you, but a
> varchar does not have to be using a fulltext index.
>
>> And if MySQL does do that, when does it stop hashing distinct string values?
>
> It doesn't do any hashing, unless you explicitly specify for it to do
> so (which can be done, I think, but I've never bothered). I don't see
> why you'd have to do any hashing for the index to be small.
>
> -Otto
>

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.

-Otto
Sent from Memphis, TN, United States