Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
Description
Making changes in the optimizer causes changes in the query plans in mtr.
Often we only see the changed EXPLAIN and it's not clear whether the change is for the better or not.
Manual analysis is labor intensive.
The idea:
We already have test runs with --mysqld=--optimizer_trace=on.
Add an mtr option to also get the trace after every query.
Running the test produces .result file (or .reject file).
There should be another file (tentative name: .result.with-traces) which will be like .result but with optimizer trace contents after every query which produces a trace.
Also write a .result.with-traces.mapping file which will have these pairs:
line_number_in_result_file: line_number_in_result_with_traces_file
|
The files should probably not be in git.
Then, when there's a difference between .result and .reject, for example:
--- /optane/dev-git2/10.6-cur2/mysql-test/main/subselect_sj_jcl6.result
|
+++ /optane/dev-git2/10.6-cur2/mysql-test/main/subselect_sj_jcl6.reject
|
@@ -2189,10 +2189,10 @@
|
explain
|
SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 PRIMARY t5 index a a 10 NULL 2 Using where; Using index; LooseScan
|
-1 PRIMARY t4 ALL NULL NULL NULL NULL 3
|
...
|
+1 PRIMARY t5 index a a 10 NULL 2 Using where; Using index; Start temporary
|
+1 PRIMARY t2 ref b b 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
|
...
|
We can see that it affects lines 2189 - (2189 + 10) in the .result file.
Look up that in the testname.result.with-traces.mapping file will give us the line numbers in the testname.result.with-traces file. The same goes for reject files. We can extract those line ranges into two files named like testname.result.with-traces.2189 and perform a diff.
(We could also diff the whole testname.{result,reject}.with-traces but those are likely to be
very large files with some changes not relevant to .result vs .reject changes.