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;
};

Looking at the cond-var I do not see anything meaningful, mostly
NULL-Ptrs.

I assume that condition pushdown is disabled by default. So what do I
have to do to enable it?

Should I use another version of MySQL/MariaDB instead?

Thanks
AugustQ





--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

Hi AugustQ,

On 02/27/2011 08:54 PM, AugustQ wrote:
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:ushed_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".


If you see your ::cond_push() function being called then this is a good
sign that engine condition pushdown is enabled.

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/...imization.html

Best regards,
Olav


--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

Hi Olav,

thanks for your fast reply. It could happen that I'm slower in
responding, sorry.

I will look into your reply more deeply in the next days and then will
come back.

AugustQ

PS: in my example I returned the function-parameter because I've read
that one could NULL those parts of the query that were handled by the
storage-engine and let the upper layer do the rest.
And, in the first step, I only want to look into the condition.
Somewhere there must be a pointer to the column-name, a hint to the
comparison-operator and the value, which I didn't find.

OK, I will give it another try.


Am Sonntag, den 27.02.2011, 22:51 +0100 schrieb Olav Sandstaa:


--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

Hi August,

check out sql/ha_ndbcluster_cond.cc in MySQL Cluster 7.0, it makes use
of the condition pushdown interface and should serve as a good example.

https://code.launchpad.net/~mysql/my...ql-cluster-7.0


Best regards
Magnus Blåudd


On 02/28/2011 09:06 PM, AugustQ wrote:

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

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:


--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw