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

Hi Martin,

On 7/20/07, Martin Friebe <martin@hybyte.com> 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

-- 
MySQL Internals Mailing List
For list archives: 
http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=lists@pantek.com