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
>
