From: Zardosht Kasheff Date: July 12 2009 5:01pm Subject: Re: help with index_merge and clustering keys List-Archive: http://lists.mysql.com/internals/37179 Message-Id: <2f9663ba0907121001p758951e9h2b334b74b0696b15@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable ah, yes, I made the mistake of making my example have equality checks instead of ranges. So instead of "=3D 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 wrot= e: > 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 =3D InnoDB >> >> Now suppose I have the query "select * from foo where a=3D1 and b =3D 1 >> and c =3D 1 and d =3D 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 >> =3D 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=3D1 and c=3D1, and then filter >> the rows retrieved where a !=3D 1. Another way is to do a range query on >> the clustered primary key where a=3D1, and filter the rows where b !=3D = 1 >> and c !=3D 1. (There are also bad query plans where we can retrieve all >> rows where b=3D1 and filter those a !=3D1 and c !=3D 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 =A0get_best_ror_intersect. > > This query is a special case since "a=3D1" is a "primary_key=3Dconst" con= dition. > 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_statisti= cs(), > search for join_read_const_table() calls. > > BR > =A0Sergey > -- > Sergey Petrunia, Software Developer > Monty Program AB, http://askmonty.org > Blog: http://s.petrunia.net/blog >