Re: the IN list optimizer speed [Re: How can I disable equality propagation in MySQL 5.0]

[Sergey Petrunia]

Hi Martin,

On 7/20/07, Martin Friebe <martin@stripped> wrote:
> Part of the problem (at least where myisam is involved) is the way the
> optimizer calculates the cost.
>
> 2 older patches to gain about 20% in the  myisam key lookup are
> http://bugs.mysql.com/bug.php?id=26232
> http://bugs.mysql.com/bug.php?id=26453
>
Yes those are on my todo. However, they provide only limited speedup.

I think the right way to resolve this is to switch from depth-first
index dives to a sort of breadth-first traversal. The first step will
be to analyze the root index page, find the fraction of leaf pages
that belong to all pages. Then, if we think we need a more precise
estimate, we could analyze the children pages, then grandchildren and
so forth.

This approach will allow to control the amount of time/disk IO we
spend in range analyzer.

BR
Sergey
--
Sergey Petrunia, Software Developer
MySQL AB, www.mysql.com
Office: N/A
Blog: http://s.petrunia.net/blog