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
