Migration from 4.1.7 to 5.0.27 queries taking 30 times as long.

[Adam McCormack]

Good morning.

We have just gone through an upgrade of our database (and the hardware it runs on) moving from MySQL 4.1.7-nt to 5.0.27-community-max-nt.

We have come across a number of queries that ran slowly after the move, taking several minutes to run, when they had run in seconds. We found that we could make them run quickly again by adding the STRAIGHT_JOIN clause to the query, understanding that the slow down may be related to a query optimizer issue. We therefor have a band aid fix to most of our problems with queries we directly control.

However have users who connect to the database via crystal reports, access, etc. who are now reporting that their queries, which used to take seconds, are now taking minutes, just as some of our systems have showed before we used the STRAIGHT JOIN. we have taken their queries and tried them in both the query browser and command line, an example from the command line, the query without "STRAIGHT_JOIN" took 3min 15.63sec, while with "STRAIGHT_JOIN" it only took 6.22 seconds.

Is this a known bug that has been patched and we just need to get fully up to date, or is there something we can do on the server end to fix this?

Thanks!

Yes, I admit, I am double posting this, it is also on the "Reporting, Charting, and BI" forum, but Ive been questioning all day if that is the right place for it, and finally came to the conclusion that this fit better, or at least as well. If either gets an answer, I will make sure to duplicate the answer as well.)