> revision-id: 500d909a795022255fb6f6c44cd310636afc0b5a > (mariadb-10.3.6-226-g500d909) > parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a > author: Varun Gupta > committer: Varun Gupta > timestamp: 2019-03-11 20:20:35 +0530 > message: > > MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly > > Keys with multi-key parts were not being printed correctly, only the first > key part > was getting printed. > Fixed it by making sure append_range_all_keyparts function is called for the > remaining keyparts. > ...
> diff --git a/mysql-test/main/opt_trace.result > b/mysql-test/main/opt_trace.result > index 4c3e2b3..a4c2b86 100644 > --- a/mysql-test/main/opt_trace.result > +++ b/mysql-test/main/opt_trace.result > @@ -6022,3 +6022,428 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, > t1 t_inner_4) { > set @@optimizer_switch= @save_optimizer_switch; > drop table t1,t2; > set optimizer_trace='enabled=off'; > +# > +# MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly > +# > +create table t0(a int); > +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); > +create table one_k (a int); > +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; > +create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d)); > +insert into t1 select a,a,a,a from one_k; > +set optimizer_trace=1; > +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1; ... > +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; This produces a lot of irrelevant output. Can you use something like select JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')) from optimizer_trace to select on the part of the trace that we need? > + "analyzing_range_alternatives": { > + "range_scan_alternatives": [ > + { > + "index": "a", > + "ranges": ["10 < a AND NULL < b < 10"], This is not a valid range. If one forces this quick select to be picked for the query plan, they can look into .trace and see the ranges: explain format=json select * from t1 force index (a) where a > 10 and b < 10; T@19 : | | | | | | | | | | >print_quick quick range select, key a, length: 5 10 < X other_keys: 0x0: T@19 : | | | | | | | | | | <print_quick (you can also try with a >=10 to see a different kind of range being produced). (One could argue that what is printed is a representation of the SEL_ARG graph, but in that case it is extremely confusing. I think, we should start with showing ranges, not SEL_ARG graphs). I think, we should not try to intrepret the SEL_ARG graph ourselves but rather use sel_arg_range_seq_init / sel_arg_range_seq_next to produce ranges that can be printed. Any objections to this? > + "rowid_ordered": false, > + "using_mrr": false, > + "index_only": false, > + "rows": 989, > + "cost": 1272.8, > + "chosen": false, > + "cause": "cost" > + }, > + { > + "index": "c", > + "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"], > + "rowid_ordered": true, > + "using_mrr": false, > + "index_only": false, > + "rows": 1, > + "cost": 2.3783, > + "chosen": true > + } > + ], BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp