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/bugs?unsub=gcdmb-bugs@xxxxxxxxxxx