Re: Test case: 4.1.3 much slower than 4.0.20d

Hello,

   thanks a lot, you are absolutely right after analyze
speed is much much better. Now I can start to use/test 4.1 for our
development apps. :)

--
Saulius

Sergei Golubchik wrote:

> Hi!
> 
> On Jul 12, SaGu wrote:
> 
>>Hello,
>>
>>  on ftp://ftp.mysql.com/pub/mysql/upload/ please find
>> file perf_test_case.rar (performance test case).
>>
>>Within RAR archive there are two files:
>>
>> - perf_test_data.sql.gz -- test case's data (~105M of data)
>> - perf_test_query.sql   -- test case's query
>>
>>Test have been done on Windows with 4.0.20d and 4.1.3-beta mysql
>>version servers.
>>
>>Query results:
>>
>>4.0.20d - ~0.14s
>>4.1.3   - ~10.61s ( ~ 43 times slower ! )
>>
>>Hope it could be useful if such results is caused by a bug.
> 
> 
> No, it's not a bug (though it took me quite a while to figure it out :)
> 
> It's a new feature that exposed a problem in your application.
> 
> Check EXPLAIN output - in 4.0 e* tables are accessed by "ref" join
> method (like in indexed_column=some_value)
> while in 4.1 it's "ref_or_null" (like in indexed_column=some_value OR
> indexed_column IS NULL). MySQL-4.0 was not able to use indexes for the
> latter expression, "ref_or_null" was implemented in 4.1.
> 
> The second plan in fact looks better - number of rows accessed by
> "ref_or_null" is less that the number of rows in 4.0 accessed with "ref".
> But in fact the plan in 4.1 is ~40 times slower - so something must be
> wrong with the cost estimation.
> 
> And this is your problem - tables are not analyzed, so MySQL cannot get
> any meaningful estimates about number of rows. After doing ANALYZE TABLE
> for all your tables, the query is very fast in 4.1 too.
> 
> Regards,
> Sergei
> 

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]