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?