MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:June 27 2009 8:08am
Subject:re: Debugging a JOIN (custom storage engine)
View as plain text  

>>>>> "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 =

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.baz =
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.


For information of MariaDB, the community developed server based on
source code from MySQL, check out
Debugging a JOIN (custom storage engine)Slava Akhmechet22 Jun
  • Re: Debugging a JOIN (custom storage engine)Philip Stoev22 Jun
    • Re: Debugging a JOIN (custom storage engine)Brian Aker22 Jun
      • Re: Debugging a JOIN (custom storage engine)Jess Balint3 Jul
        • Re: Debugging a JOIN (custom storage engine)MARK CALLAGHAN3 Jul
          • Re: Debugging a JOIN (custom storage engine)Jonas Oreland3 Jul
    • Re: Debugging a JOIN (custom storage engine)Slava Akhmechet22 Jun
  • re: Debugging a JOIN (custom storage engine)Michael Widenius27 Jun
  • Re: Debugging a JOIN (custom storage engine)Slava Akhmechet6 Jul