From: AugustQ
Date: March 6 2011 5:24pm
Subject: Re: condition pushdown
List-Archive: http://lists.mysql.com/internals/38264
Message-Id: <1299432275.5449.4.camel@AMD4>
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

Hi Olav,

thanks for your advice.

I modified ha_dbf::cond_puush() 
to return NULL. Now explain tells me that condition pushdown is used.

So my next step will be looking at the COND-typedef and trying to find
my WHERE-clause.

Tanks.
AugustQ

Am Sonntag, den 27.02.2011, 22:51 +0100 schrieb Olav Sandstaa:
> Hi AugustQ,
> 
> On 02/27/2011 08:54 PM, AugustQ wrote:
> > Hi,
> >
> > how can I enable condition pushdown?
> >
> > This is my first question here on this list. I'm working as a
> > software-developer for quite some time and currently I am working in a
> > DWH-project here in Germany.
> >
> > I found these sources: mysql-5.5.0-m2
> > and got it to compile and work here on my machine (Ubuntu 64bit).
> > After some reading (books from Hutchings/Golubchik and Pachev) I decided
> > to play a bit with storage engines, so I wrote my tiny storage engines
> > (which work, so the basics are understood, at least in parts).
> >
> > The next topic I wanted to play with is condition pushdown but I had no
> > luck until now.
> > In mysql I can do
> >      set engine_condition_pushdown=1
> > and got a result OK.
> > When I enter
> >      explain select * from prob_alt where rv_nr = '12345678'\G
> > I can only see 'using where', nothing more.
> >
> > I've set a breakpoint to the following piece of code:
> >
> >      const COND * ha_dbf::cond_push(const COND *cond)
> >      {
> >          myCond = (COND *)cond;
> >          return cond;
> >      };
> 
> I think it would be more correct if you returned NULL from this 
> function. The intention is that ::cond_push() should return the part of 
> the condition that the storage engine will not evaluate. So when you 
> return the original condition the server assumes that the storage engine 
> did not want to handle this condition. See the following comment about 
> the return value in sql/handler.h:
> 
>     @return
>       The 'remainder' condition that caller must use to filter out records.
>       NULL means the handler will not return rows that do not match the
>       passed condition.
> 
> You can also have a look at the following code in sql/sql_select.cc 
> where the condition is pushed to the storage engine:
> 
>            /* Push condition to storage engine if this is enabled
>               and the condition is not guarded */
>        if (thd->variables.optimizer_switch &
>                OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN)
>            {
>              COND *push_cond=
>                make_cond_for_table(tmp, tab->table->map, tab->table->map);
>              if (push_cond)
>              {
>                /* Push condition to handler */
>                if (!tab->table->file->cond_push(push_cond))
>                  tab->table->file->pushed_cond= push_cond;
>              }
>            }
> 
> This shows that if the ::cond_push() function returns anything else than 
> NULL then the handler::pushed_cond will not get set. This is the 
> variable that is checked by explain to determine if it should print 
> "Using where with pushed condition" or just print "Using where".
> 
> 
> > Looking at the cond-var I do not see anything meaningful, mostly
> > NULL-Ptrs.
> 
> If you see your ::cond_push() function being called then this is a good 
> sign that engine condition pushdown is enabled.
> 
> > I assume that condition pushdown is disabled by default. So what do I
> > have to do to enable it?
> 
> In the MySQL 5.5 codebase engine condition pushdown should be enabled by 
> default so you should not have to do anything to enable it. See 
> documentation:
> 
> http://dev.mysql.com/doc/refman/5.5/en/condition-pushdown-optimization.html
> 
> Best regards,
> Olav
> 
>