From: Rick James
Date: June 16 2011 6:49pm
Subject: Re: Regression test returning different result each time it's run
List-Archive: http://lists.mysql.com/internals/38342
Message-Id: <4DFA5048.5060900@yahoo-inc.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

InnoDB's estimates of Rows varies by up to about a factor of 2, based on 
the phase of the moon.

On 6/16/11 7:32 AM, Øystein Grøvlen wrote:
> 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=hiromichiwatari@stripped
>>>
>>>
>

-- 
Rick James - MySQL Geek