Re: mysql is a pain in the ass

[Michael Widenius]

>>>>> "Frederik" == Frederik Lindberg <lindberg@stripped>
> writes:

<cut>

Frederik> I expect it's not trivial. I would expect the key to be indexed as a
Frederik> [balanced] tree. You enter at the root, and within a few comparisons,
Frederik> you're at 'foo...'. Now all others are in the branches of your node. No
Frederik> such thing for '%foo', for which you'd have to scan the entire index. For
Frederik> LIKE '%foo' I'd create an index for the inverse ('oof...'), but I don't
Frederik> know how one would optimize LIKE '%foo%'. Also, don't know the MySQL
Frederik> internals. Maybe it is very easy to speed up the index scan, or maybe
Frederik> MySQL incorrectly decides that the index is worthless (even though a
Frederik> complete index scan is cheaper than a complete data scan).

The above isn't really true;  If the records are short, it's faster to 
scan the table than scan the index (as scanning the index involves
much more seeks)

<cut> (When talking about analyzing)

>> To me, this is magic.  It may require the most massive changes to the internal
>> structure of MySQL, for all I know.  It may also be a large part of the size of
>> oracle :)

Frederik> I thought that some ISAM index optimizing commands do just that (but less
Frederik> visibly).
 
Yes, MySQL is not as big need of a analyze command as Oracle, as MySQL
can, with very low overhead, find out the key distribution from the
'live' index.

Regards,
Monty