Re: Joins - done in NDB or MySQL?

[Harrison Fisk]

Hi,

The join itself is done at the upper MySQL level. With that said some of the work should still be done in the NDB side as well depending on indexes (or even without indexes in future 5.0 versions).
I would have to see an EXPLAIN to know for sure, but in your above example if you had indexes correct, I think MySQL would execute the query in the following way:

Read the 100000 rows to local storage using a remote index access if you had a WHERE condition. Loop thru each of the 100000 rows retrieving the 2000 rows neccesary for each of the matches using a remote index lookup. That is a rough estimation of how it will be done. It actually will do it a bit differently, but that is a rough idea.

So it sounds like it will do what you want it to do. Your query sounds like it will return a huge amount of rows. 100000*2000 would be 200,000,000 rows being returned. I hope you were just giving an example and not really trying to return that much data.

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com