List:Internals« Previous MessageNext Message »
From:Sergey Petrunya Date:July 12 2009 10:46am
Subject:Re: help with index_merge and clustering keys
View as plain text  
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