Hi Øystein,
Thank you for your pointer, that was very helpful.
Hiromichi
--- On Thu, 6/16/11, Øystein Grøvlen <oystein.grovlen@stripped>
wrote:
> From: Øystein Grøvlen <oystein.grovlen@stripped>
> Subject: Re: Regression test returning different result each time it's run
> To: "Hiromichi Watari" <hiromichiwatari@stripped>
> Cc: internals@stripped
> Date: Thursday, June 16, 2011, 10:32 AM
> On 16/06/2011 16:10, Hiromichi Watari
> wrote:
> > Hi Øystein,
> > Thank you for your reply.
> >
> > I am using the same storage engine (Innob I believe)
> and repeating the test results in different output each
> time.
>
> Hi,
>
> By default, InnoDB statistics will vary slightly each time
> you start the
> server. Hence, you will get different output. (See my
> blog
> http://oysteing.blogspot.com/2011/04/more-stable-query-execution-time-by.html
>
> for a discussion about this.)
>
> By default, the select test uses the MyISAM storage engine
> which
> provides exact statistics.
>
> In order to mask such row count differences, a test can use
> the
> replace_column command:
>
> --replace_column 9 x
> explain select ...
>
>
> Hope this helps,
>
> --
> Øystein
>
> >
> > Hiromichi
> >
> >
> > --- On Thu, 6/16/11, Øystein
> Grøvlen<oystein.grovlen@stripped>
> wrote:
> >
> >> From: Øystein Grøvlen<oystein.grovlen@stripped>
> >> Subject: Re: Regression test returning different
> result each time it's run
> >> To: "Hiromichi Watari"<hiromichiwatari@stripped>
> >> Cc: internals@stripped
> >> Date: Thursday, June 16, 2011, 3:22 AM
> >> On 15/06/2011 17:08, Hiromichi Watari
> >> wrote:
> >>> Hi,
> >>>
> >>> I'm running a regression test
> >> (/mysql-test/t/select.test) on 5.5 server
> (LaunchPad rev
> >> 3487) but each time I run the test I get different
> number of
> >> rows in explain select statements.
> >>> Is this normal behavior ? Or am I
> missing
> >> something ?
> >>
> >> The difference is in the estimated number of rows
> in table
> >> t2. This
> >> number is provided by the storage engine.
> Maybe you
> >> are using a
> >> different storage engine?
> >>
> >> Hope this helps,
> >>
> >> --
> >> Øystein Grøvlen, Principal Software Engineer
> >> MySQL Group, Oracle
> >> Trondheim, Norway
> >>
> >>>
> >>> Thanks,
> >>> Hiromichi
> >>>
> >>> p.s. I start up the server first then issue
> the
> >> following command,
> >>>
> >>> [hwatari@desktop-intel mysql-test]$ sudo
> >> ./mysql-test-run.pl --extern
> socket=/tmp/mysql.sock
> >> t/select.test
> >>>
> >>>
> >>>
> >>
> -------------------------------------------------------------------------
> >>>
> >>>
> >>> ---
> >>
> /usr/local/mysql-5.5-3487/mysql-test/r/select.result
> >> 2011-05-03 15:00:59.000000000 +0300
> >>> +++
> >>
> /usr/local/mysql-5.5-3487/mysql-test/r/select.reject
> >> 2011-06-15 17:09:43.126761202 +0300
> >>> @@ -133,10 +133,10 @@
> >>> 1
> >> SIMPLE t2
> >> ref fld3
> >> fld3 30
> >> const 1 Using where;
> >> Using index
> >>> explain select fld3
> from t2 ignore
> >> index (fld3) where fld3 = 'honeysuckle';
> >>> id
> >> select_type table
> >> type possible_keys
> >> key key_len
> >> ref rows Extra
> >>> -1 SIMPLE
> >> t2 ALL
> >> NULL NULL
> >> NULL NULL
> >> 1199 Using where
> >>> +1 SIMPLE
> >> t2 ALL
> >> NULL NULL
> >> NULL NULL
> >> 1209 Using where
> >>>
> >>>
> >>
> >>
> >> --
> >> MySQL Internals Mailing List
> >> For list archives: http://lists.mysql.com/internals
> >> To unsubscribe: http://lists.mysql.com/internals?unsub=1
> >>
> >>
>
>
> --
> Øystein Grøvlen, Principal Software Engineer
> MySQL Group, Oracle
> Trondheim, Norway
>
