How is it going?

I might completely have misunderstood how NLJ works. It might be opposing. My comment below describes how it works now, doesn’t it? I had a wrong idea like “it firstly scans the driving table. then it fetches the following tables in turn” The my previous wrong idea is very much like BKA, isn’t it? BKA joins fetches all satisfied rows to the WHERE clauns from the driving table firstly, then fetches matching rows from the following table. Is this correct?

I’m looking forward to that MRR+sort will be implemented.

Another thing what I want you to consider to implement in the future is an optimization for LIMIT clause. Currently, LIMIT will take effect after all JOIN and filesort is done. It’s okay for the third example above. However, building a whole JOIN result might be unnecessary for first and second example. If LIMIT is evaluated earlier, roundtrips can be reduced dramatically in some cases.

Suppose that the query has “LIMIT 3″ clause. For the first example, MySQL optimizer executes the query like below:

o Fetch all matching rows from tbl1 in index order.
o Fetch all matching rows from tbl2 and join.
o Fetch all matching rows from tbl3 and join.
o Limit rows and returns 3 rows to the client.

If MySQL optimizer has an alternative plan like below, it looks more efficient than now:

o Fetch the first row from tbl1 in index order.
o Fetch matching rows from tbl2 and join.
o Fetch matching rows from tbl3 and join.
o At this point the number of rows is less than 3, then continue.
o Fetch the next row from tbl1 in index order.
o Fetch matching rows from tbl2 and join.
o Fetch matching rows from tbl3 and join.
o At this point the number of rows is greater than or equal to 3, then returns 3 rows to the client.

I see many web applications use LIMIT clause with sort. Having a query plan like this may improve query performance in certain cases IMHO

With regards to MRR and ordering:

* MRR/NDB is capable of producing ordered output (it will sort if asked to).

* MyISAM/Maria/Falcon/InnoDB-MRR are incapable of producing records in any particular order. I agree that sometimes it could be faster to use MRR+sort (especially since we don\’t need to sort the complete set, we\’ll need to only restore index ordering within each of the MRR passes). That\’s just not implemented. We\’ve decided to be conservative and disable MRR (other, more agressive option was to force sort if MRR is used)

you\’re welcome to cite/copy pictures.

I wonder why MRR is disabled when ordering is required. I guess getting rows using MRR then filesort() may perform better than disabling MRR in some cases, especially for remote storage engines like NDB, because filesourt() could be faster than round trips between indexes.

BTW, can I cite these your pictures and introduce in my blog to Japanese people? They’re really nice and intuitive

Yes (and yes, this is counterintuitive and I hope we’ll fix it at some point)

> I’m having trouble finding a query that will produce such an EXPLAIN plan, so I hope this isn’t an obvious question.

Here is an example:
mysql> explain select * from const_tbl, t1, t2 where t1.b=const_tbl.a and t2.a=t1.a order by t1.a;
+—-+————-+———–+——–+—————+——+———+————+——+—————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———–+——–+—————+——+———+————+——+—————-+
| 1 | SIMPLE | const_tbl | system | NULL | NULL | NULL | NULL | 1 | Using filesort |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
| 1 | SIMPLE | t2 | ref | a | a | 5 | test4.t1.a | 11 | Using where |
+—-+————-+———–+——–+—————+——+———+————+——+—————-+
3 rows in set (0.01 sec)

Both yes and no. I’m going to have this question sorted out (for myself too in the mentioned forthcoming posts.

> would it from the Server development’s point of view be more or less complex if MySQL would not commit itself to returning the set in the order implied by the GROUP BY clause?

It would be possible to skip the sorting step sometimes, but as long as we use current apporaches to handling GROUP BY, there will be close relationship between GROUP BY and ORDER BY processing. Again, I hope to cover this in the next posts.