List: Internals « Previous MessageNext Message » From: Zardosht Kasheff Date: July 12 2009 5:01pm Subject: Re: help with index_merge and clustering keys View as plain text
```ah, yes, I made the mistake of making my example have equality checks

So instead of "= 1", suppose each of the range checks are "> 1" (or >
100000, some value where the optimizer needs records_in_range to
determine the cost estimate of using a key).

So for the simple example Rich mentioned, "select * from foo where a >
1 and b > 1"
The more complex example, "select * from foo where a > 1 and b > 1
and c > 1 and d > 1"

What happens here?

Thanks
-Zardosht

On Sun, Jul 12, 2009 at 6:46 AM, Sergey Petrunya<psergey@stripped> wrote:
> On Fri, Jul 10, 2009 at 09:25:47AM -0400, Zardosht Kasheff wrote:
>> For the internals list, I also have a question of my own about the
>> optimizer. Suppose I have an InnoDB table:
>> create table foo (a int, b int, c int, d int ..., primary key (a),
>> key(b), key(c))engine = InnoDB
>>
>> Now suppose I have the query "select * from foo where a=1 and b = 1
>> and c = 1 and d = 1".
>>
>> The general way to answer this query is to use the keys to retrieve
>> all rows were a, b, and c are all 1, and then filter the rows where d
>> = 1. There seem to be two ways to retrieve all rows where a, b, and c
>> are all 1. One is to do an intersect of b=1 and c=1, and then filter
>> the rows retrieved where a != 1. Another way is to do a range query on
>> the clustered primary key where a=1, and filter the rows where b != 1
>> and c != 1. (There are also bad query plans where we can retrieve all
>> rows where b=1 and filter those a !=1 and c != 1).
>>
>> Where is the logic that makes that determines these specific options?
>> Unless I do not understand the code correctly, it does not seem to be
>> in  get_best_ror_intersect.
>
> This query is a special case since "a=1" is a "primary_key=const" condition.
> Presense of such condition makes foo a "constant table". constant tables are
> detected early in optimization phase, so that columns of constant tables can be
> treated as known constants in all subsequent stage.
>
> In the code, const table detection is in sql_select.cc:make_join_statistics(),
>
> BR
>  Sergey
> --
> Sergey Petrunia, Software Developer
> Blog: http://s.petrunia.net/blog
>
```
help with index_merge and clustering keysZardosht Kasheff23 Jun
• Re: help with index_merge and clustering keysZardosht Kasheff9 Jul
• Re: help with index_merge and clustering keysSergey Petrunya10 Jul
• Re: help with index_merge and clustering keysZardosht Kasheff10 Jul
• RE: help with index_merge and clustering keysRick James10 Jul
• RE: help with index_merge and clustering keysMichael Widenius16 Jul
• Re: help with index_merge and clustering keysZardosht Kasheff16 Jul
• RE: help with index_merge and clustering keysRick James16 Jul
• RE: help with index_merge and clustering keysMichael Widenius19 Jul
• Re: help with index_merge and clustering keysSergey Petrunya12 Jul
• Re: help with index_merge and clustering keysZardosht Kasheff12 Jul
• Re: help with index_merge and clustering keysSergey Petrunya15 Jul
• RE: help with index_merge and clustering keysRick James15 Jul
• Re: help with index_merge and clustering keysZardosht Kasheff24 Jul
• Re: help with index_merge and clustering keysMichael Widenius16 Jul
Re: help with index_merge and clustering keysZardosht Kasheff10 Jul
Re: help with index_merge and clustering keysZardosht Kasheff16 Jul
• RE: help with index_merge and clustering keysRick James16 Jul
• Re: help with index_merge and clustering keysZardosht Kasheff16 Jul