On Jul 12, SaGu wrote:
> 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.
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany