maria-developers team mailing list archive

[Anshu Avinash <anshu.avinash35@xxxxxxxxx>]

Hi serg,

Yes, I had tried that. I had tried many combinations. I have updated the
test however on github code:
https://github.com/igniting/server/tree/selfTuningOptimizer.

Regards
Anshu


On Wed, Jun 11, 2014 at 1:13 PM, Sergei Golubchik <serg@xxxxxxxxxxx> wrote:

> Hi, Anshu!
>
> On Jun 11, Anshu Avinash wrote:
> > Hi serg,
> >
> > I have attached the updated diff. I'm still unable to observe the effect
> > introduced by the cost factors. Maybe I just need to study the usage of
> > scan_time() and read_time() in greater detail.
>
> Ok, see one suggestion below. If that won't help - push your changes to
> github, I'll try myself.
>
> > diff --git a/mysql-test/r/costmodel.result
> b/mysql-test/r/costmodel.result
> > new file mode 100644
> > index 0000000..6668758
> > --- /dev/null
> > +++ b/mysql-test/r/costmodel.result
> > @@ -0,0 +1,20 @@
> > +DROP TABLE IF EXISTS t1;
> > +CREATE TABLE t1 (a int auto_increment primary key, b int);
> > +INSERT INTO t1(b) values
> (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
> > +(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25);
> > +EXPLAIN
> > +SELECT * FROM t1
> > +WHERE a > 21;
> > +id   select_type     table   type    possible_keys   key     key_len
> ref     rows    Extra
> > +1    SIMPLE  t1      range   PRIMARY PRIMARY 4       NULL    5
> Using index condition
> > +use mysql;
> > +UPDATE optimizer_cost_factors
> > +SET const_value=2.0
> > +WHERE const_name='SCAN_TIME_FACTOR';
> > +use test;
> > +EXPLAIN
> > +SELECT * FROM t1
> > +WHERE a > 21;
> > +id   select_type     table   type    possible_keys   key     key_len
> ref     rows    Extra
> > +1    SIMPLE  t1      range   PRIMARY PRIMARY 4       NULL    5
> Using index condition
> > +DROP TABLE t1;
>
> See? Your first EXPLAIN already shows type=range. And then you increase
> the cost of the table scan. Of course, nothing can happen anymore.
>
> Try to make the first explain to show type=ALL (which means table scan),
> then increase the cost of the table scan and see how optimizer will
> switch to type=range.
>
> Regards,
> Sergei
>
>