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
