New DS-MRR optimizations must be visible in EXPLAIN

[Philip Stoev]

As discussed previously, it is imperative for proper automatic testing that the new DSS-MRR-CPK optimizations announce themselves in EXPLAIN . The current "using join buffer" is too generic to be usful. Another string in the Extra column would be required, or , at the very least, some indication in EXPLAIN EXTENDED + SHOW WARNINGS. If the optimization is decided on runtime, some other method of knowing that it has triggered must be provided.

Also, modifying the optimizer variable @mrr_sort_keys does not cause any visible changes in the EXPLAIN. We have queries that are experiencing performance improvements and regressions due to the recent DS-MRR optimizations, but the EXPLAIN plan does not change even a single bit.

[Sergey Petrunia (sergefp)]

Fix proposal and discussion here: https://lists.launchpad.net/maria-developers/msg03804.html

[Sergey Petrunia (sergefp)]

The EXPLAIN part have been addressed: EXPLAIN now shows whether key ordering, rowid ordering, or both of them were performed:

http://lists.askmonty.org/pipermail/commits/2011-April/001493.html
http://lists.askmonty.org/pipermail/commits/2011-April/001498.html

[Philip Stoev (pstoev-askmonty)]

SergeyP, unfortunately your solution has disconnected the EXPLAIN output from the optimizer switches that control it. In other words, a user would not know that if he gets a " Rowid-ordered scan" , he should use optimizer_use_mrr=disable to disable it. Previously, MRR was mentioned both in EXPLAIN and in the mysqld option.