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
instead of ranges.

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(),
> search for join_read_const_table() calls.
>
> BR
>  Sergey
> --
> Sergey Petrunia, Software Developer
> Monty Program AB, http://askmonty.org
> Blog: http://s.petrunia.net/blog
>
Thread
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