Re: Test case: 4.1.3 much slower than 4.0.20d - Sergei Golubchik - com.mysql.lists.bugs

3 messages in com.mysql.lists.bugsRe: Test case: 4.1.3 much slower than...

From	Sent On	Attachments
SaGu	12 Jul 2004 02:26
Sergei Golubchik	12 Jul 2004 09:01
SaGu	12 Jul 2004 23:10

Subject:	Re: Test case: 4.1.3 much slower than 4.0.20d
From:	Sergei Golubchik (se...@mysql.com)
Date:	07/12/2004 09:01:24 AM
List:	com.mysql.lists.bugs

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