List:Internals« Previous MessageNext Message »
From:Philip Stoev Date:June 22 2009 10:27am
Subject:Re: Debugging a JOIN (custom storage engine)
View as plain text  
Hello,

What MySQL version are you using? If it is 6.0, then do you see MRR 
mentioned in your EXPLAIN? If so, please set optimizer_use_mrr=disable and 
try again.

Thank you.

Philip Stoev

----- Original Message ----- 
From: "Slava Akhmechet" <coffeemug@stripped>
To: <internals@stripped>
Sent: Monday, June 22, 2009 11:54 AM
Subject: Debugging a JOIN (custom storage engine)


> I'm debugging a join in our custom storage engine and I'm seeing
> behavior I don't understand how to debug.
>
> We have two tables, each one has three columns (foo, bar, baz). In
> both tables foo is unindexed, bar is a primary key, and baz is a
> secondary index. We're debugging a very simple join query:
>
> select *
> from t1, t2
> where t1.foo = t2.foo
>
> This query works perfectly fine. Similarly, if I change the condition
> to any of the following, the query works fine as well:
>
> t1.foo=t2.baz
> t1.baz = t2.foo
> t1.baz = t2.baz
>
> However, the moment we introduce bar into the where condition (the
> primary key), the query returns zero rows. I can select for these rows
> separately, but they're not discovered in a join. The only difference
> I see in EXPLAIN is that the primary key causes an eq_ref join (vs. a
> ref join). I've looked through the logs, and it looks like end_send
> (followed by send_data) never gets called when I introduce bar into
> the where condition. However, the storage engine index code finds the
> appropriate row just fine. I'm not sure what causes MySQL to decide to
> ignore the match.
>
> I'm not sure how to debug this. Does anyone have any pointers that might 
> help?
>
> Thanks,
> - Slava Akhmechet
>
> -- 
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
> 

Thread
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