From: Michael Widenius
Date: June 27 2009 8:08am
Subject: re: Debugging a JOIN (custom storage engine)
List-Archive: http://lists.mysql.com/internals/37012
Message-Id: <19013.54133.282182.105627@narttu.askmonty.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hi!

>>>>> "Slava" == Slava Akhmechet <coffeemug@stripped> writes:

Slava> I'm debugging a join in our custom storage engine and I'm seeing
Slava> behavior I don't understand how to debug.

Slava> We have two tables, each one has three columns (foo, bar, baz). In
Slava> both tables foo is unindexed, bar is a primary key, and baz is a
Slava> secondary index. We're debugging a very simple join query:

Slava> select *
Slava> from t1, t2
Slava> where t1.foo = t2.foo

Slava> This query works perfectly fine. Similarly, if I change the condition
Slava> to any of the following, the query works fine as well:

Slava> t1.foo=t2.baz
Slava> t1.baz = t2.foo
Slava> t1.baz = t2.baz

Slava> However, the moment we introduce bar into the where condition (the
Slava> primary key), the query returns zero rows. I can select for these rows
Slava> separately, but they're not discovered in a join. The only difference
Slava> I see in EXPLAIN is that the primary key causes an eq_ref join (vs. a
Slava> ref join). I've looked through the logs, and it looks like end_send
Slava> (followed by send_data) never gets called when I introduce bar into
Slava> the where condition. However, the storage engine index code finds the
Slava> appropriate row just fine. I'm not sure what causes MySQL to decide to
Slava> ignore the match.

Slava> I'm not sure how to debug this. Does anyone have any pointers that might help?

Configure MySQL with --full-debug and start mysqld with:

mysqld --debug &

Then run both queries in a client

Check the difference in traces for the two queries in the trace file
/tmp/mysqld.trace. This should enable you to quickly find the problem.

Regards,
Monty

For information of MariaDB, the community developed server based on
source code from MySQL, check out www.askmonty.org