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
>
