List:Internals« Previous MessageNext Message »
From:Antony T Curtis Date:March 6 2001 12:05pm
Subject:Re: Optimisation suggestion...
View as plain text  
Michael Widenius wrote:


> >> The problem is that the above will only work if a major part of the
> >> keys are identical in the first n-1 parts. (You would guess that at
> >> least 1/3 of the keys must be identical per key block for this to
> >> work, which is not a very common situation).
> >>
> >> The major problem is that when it comes to scan indexes compared to
> >> scan a fixed length data file, index scanning is at least 3 times
> >> slower than data file scanning (for B-trees).  The reason is that
> >> there is much more seeks involved when scanning the index structure
> >> and also because the index structure is more complicated than the data
> >> file structure, especially when we talk about compressed indexes.
> Antony> This method for searches is currently used by the legacy database used
> Antony> here where the developers hand-write it for where they want to use it -
> Antony> they call it bounce/bump index searches.
> Do your legacy database save the index separately or together with the
> data?

They're stored seperately - a different file for each index actually.
The actual method how the index is implemented is similar to Btreve.
However, the size of each row is large and there are often millions of
rows in each table that searches through the indicies can be
significantly faster than a brute-force search through the table.

For non-trivial searches which are able to make use of the indicies,
MySQL is at least as fast as the purpose-written legacy code, in some
cases, faster. However for the searches for which MySQL resorts to
brute-force searches, the legacy system is still an order of magnitude

> Antony> Perhaps it could be made available on a per-table or per-handler (as a
> Antony> flag), or even some variable the user may set.
> If we do something like this, we would have an option in the handler
> if this optimization is a good thing for this table handler; We
> already do a lot of things like that.

I shall gear my patch upon using a new option flag in the handler...
Perhaps HA_USE_BOUNCESEARCH (or something like that, unless you have a
better name for it)


ANTONY T CURTIS                     Tel: +44 (1635) 36222
Abacus Polar Holdings Ltd           Fax: +44 (1635) 38670
> Isn't it strange that the same people that laugh at gypsy fortune
> tellers take economists seriously?
character set conf fileChi-Wai Lau21 Feb
  • character set conf fileMichael Widenius21 Feb
  • Optimisation suggestion...Antony T Curtis21 Feb
    • Re: Optimisation suggestion...Thimble Smith21 Feb
  • Re: Optimisation suggestion...Antony T Curtis21 Feb
    • Re: Optimisation suggestion...Michael Widenius5 Mar
  • Re: Optimisation suggestion...Antony T Curtis5 Mar
    • Re: Optimisation suggestion...Michael Widenius5 Mar
  • Re: Optimisation suggestion...Antony T Curtis6 Mar
RE: Optimisation suggestion...Enrique Villar6 Mar
  • Re: Optimisation suggestion...Antony T Curtis6 Mar