I am migrating a company PHP/mysql application to a new Linux machine, and
while doing so, am going from mysql 4.1 to 5.0. I have transferred the
database successfully using mysqldump from the old machine to produce a
file of sql commands, and then reading in this file on the new machine.

The database data and table structure are therefore the same on both
machines. I am using MyIsam tables.

Here is the problem: One of my queries is rather complicated, involving
several table joins, and one of the joins is on a derived table in order
to select the maximum value of an attribute. This query runs with mysql
4.1 in a fraction of a second. But the very same query takes over 5
seconds to run on the 5.0 machine.

If I do an "explain" on the query on both machines, all columns in the
explain results table are the same except for some differences in the
"extra" column. But the ordering of the rows in the explain table is
different for the two mysql versions.

Before I go into such detail as presenting the queries, table structures,
etc. here, I would like to ask if anyone can explain what is going on
here? Why does mysql 5.0 take so much longer to perform this query than
the older version 4.1, if the table structures and data of the database
are the same?

I see myself having two choices: downgrade mysql on the new machine to
version 4.1, but this is something that I really do not wish to do, as I
wish to keep mysql more up to date. The other choice is to keep adding
indexes and modifying the query on the 5.0 machine to speed it up. But
still, the basic problem is there: the query worked fast and well on 4.1
but not on 5.0, and that doesn't really sound like progress to me

Can anyone help?

Steve, Denmark

coolsti <coo@nospam.com> wrote:

> I am migrating a company PHP/mysql application to a new Linux machine, and
> while doing so, am going from mysql 4.1 to 5.0.
....

> One of my queries is rather complicated, involving
> several table joins, and one of the joins is on a derived table in order
> to select the maximum value of an attribute. This query runs with mysql
> 4.1 in a fraction of a second. But the very same query takes over 5
> seconds to run on the 5.0 machine.
>
> If I do an "explain" on the query on both machines, all columns in the
> explain results table are the same except for some differences in the
> "extra" column. But the ordering of the rows in the explain table is
> different for the two mysql versions.

The order of rows in explain is the order used to access the tables in
the processing of the join. So order *is* important.

> Before I go into such detail as presenting the queries, table structures,
> etc. here, I would like to ask if anyone can explain what is going on
> here? Why does mysql 5.0 take so much longer to perform this query than
> the older version 4.1, if the table structures and data of the database
> are the same?

Obviously 5.0 uses a different execution plan than 4.1. And it seems
the new plan is worse than the old one. The planner/optimizer in 5.0
was completely rewritten and handles some critical situations much
better than the old one (i.e. it handles outer joins correctly now).
However some people see situations like you where the new optimizer
uses a suboptimal plan.

You can however hint the optimizer:
http://dev.mysql.com/doc/refman/5.0/...er-issues.html
(follow the links inside)


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

> Obviously 5.0 uses a different execution plan than 4.1. And it seems
> the new plan is worse than the old one. The planner/optimizer in 5.0
> was completely rewritten and handles some critical situations much
> better than the old one (i.e. it handles outer joins correctly now).
> However some people see situations like you where the new optimizer
> uses a suboptimal plan.
>
> You can however hint the optimizer:
> http://dev.mysql.com/doc/refman/5.0/...er-issues.html
> (follow the links inside)
>
>
> XL

Hi XL and thank you very much for the informative reply!

Ok, I understand the situation better now, and will refer to the link you
provided.

In the meantime I added one single extra index to one of the tables
involved in the query, and this brought the execution speed of the query
to well under a second, like I experience on the 4.1 machine. It could
perhaps be argued that this index should have been made in the first
place, as it drastically reduces the number of rows examined (according to
"explain") on both machines. So even though the query ran fast enough on
the 4.1 machine, I probably should have had it there as well.

Thanks again for the help!

- Steve, Denmark