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=1
>>>
>>>
>
--
Rick James - MySQL Geek
