# Poor performance on joins after MYSQL 4 -\> 5 migration **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [January 28, 2008, 2:38pm UTC](https://forums.percona.com/t/poor-performance-on-joins-after-mysql-4-5-migration/613 "2008-01-28T14:38:40Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![mccormack\_adam](https://avatars.discourse-cdn.com/v4/letter/m/ac8455/32.png) [@mccormack\_adam](https://forums.percona.com/u/mccormack_adam)\ **Post date:** [January 28, 2008, 2:38pm UTC](https://forums.percona.com/t/poor-performance-on-joins-after-mysql-4-5-migration/613/1 "2008-01-28T14:38:40Z")
Good Afternoon, 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. I tried several things this weekend on a seperate installation, first installing a fresh version of 5.0.45 and then adding the tables from a mysqldump. I got the equivalent results: SELECT sbtshrink.store\_id, sbtshrink.plt\_rte\_no, sbtshrink.count\_date, sbtshrink.beg\_inv\_date, sbtshrink.beg\_inv\_units, sbtshrink.net\_del\_units, sbtshrink.sold\_units, sbtshrink.book\_units, sbtshrink.phys\_units, sbtshrink.shrink\_units, sbtshrink.shrink\_dol, area\_sales.area\_sales\_mgr, sbtshrink.bread\_cake\_code, profit\_center.profit\_center\_name, dunsmaster.duns\_name FROM ((ecommerce.sbtshrink sbtshrink INNER JOIN ecommerce.area\_sales area\_sales ON sbtshrink.area\_sales=area\_sales.area\_sales) INNER JOIN ecommerce.profit\_center profit\_center ON sbtshrink.profit\_center=profit\_center.profit\_center) INNER JOIN ecommerce.dunsmaster dunsmaster ON sbtshrink.duns\_id=dunsmaster.duns\_id WHERE sbtshrink.count\_date={d ‘2008-01-18’} AND dunsmaster.duns\_name=‘CUSTOMER’ ORDER BY sbtshrink.bread\_cake\_code, area\_sales.area\_sales\_mgr, sbtshrink.count\_date runs in ------ 7 min 0.69 sec Meanwhile SELECT STRAIGHT\_JOIN sbtshrink.store\_id, sbtshrink.plt\_rte\_no, sbtshrink.count\_date, sbtshrink.beg\_inv\_date, sbtshrink.beg\_inv\_units, sbtshrink.net\_del\_units, sbtshrink.sold\_units, sbtshrink.book\_units, sbtshrink.phys\_units, sbtshrink.shrink\_units, sbtshrink.shrink\_dol, area\_sales.area\_sales\_mgr, sbtshrink.bread\_cake\_code, profit\_center.profit\_center\_name, dunsmaster.duns\_name FROM ((ecommerce.sbtshrink sbtshrink INNER JOIN ecommerce.area\_sales area\_sales ON sbtshrink.area\_sales=area\_sales.area\_sales) INNER JOIN ecommerce.profit\_center profit\_center ON sbtshrink.profit\_center=profit\_center.profit\_center) INNER JOIN ecommerce.dunsmaster dunsmaster ON sbtshrink.duns\_id=dunsmaster.duns\_id WHERE sbtshrink.count\_date={d ‘2008-01-18’} AND dunsmaster.duns\_name=‘CUSTOMER’ ORDER BY sbtshrink.bread\_cake\_code, area\_sales.area\_sales\_mgr, sbtshrink.count\_date runs in ------ 1.97 sec I have tried changing the optimizer\_prune\_level and optimizer\_search\_depth neither of which seamed to give any resolution. On running examine for the one without STRAIGHT\_JOIN (the slow query) I get \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: area\_sales type: ALL possible\_keys: PRIMARY key: NULL key\_len: NULL ref: NULL rows: 74 Extra: Using temporary; Using filesort \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: sbtshrink type: ref possible\_keys: duns\_id,count\_date,profit\_center,area\_sales key: area\_sales key\_len: 10 ref: ecommerce.area\_sales.area\_sales rows: 641 Extra: Using where \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: dunsmaster type: eq\_ref possible\_keys: PRIMARY key: PRIMARY key\_len: 11 ref: ecommerce.sbtshrink.duns\_id rows: 1 Extra: Using where \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: profit\_center type: eq\_ref possible\_keys: PRIMARY key: PRIMARY key\_len: 9 ref: ecommerce.sbtshrink.profit\_center rows: 1 Extra: 4 rows in set (0.00 sec) When running it with STRAIGHT\_JOIN I see: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: sbtshrink type: ref possible\_keys: duns\_id,count\_date,profit\_center,area\_sales key: count\_date key\_len: 4 ref: const rows: 21078 Extra: Using where; Using temporary; Using filesort \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: area\_sales type: eq\_ref possible\_keys: PRIMARY key: PRIMARY key\_len: 9 ref: ecommerce.sbtshrink.area\_sales rows: 1 Extra: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: profit\_center type: eq\_ref possible\_keys: PRIMARY key: PRIMARY key\_len: 9 ref: ecommerce.sbtshrink.profit\_center rows: 1 Extra: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: dunsmaster type: eq\_ref possible\_keys: PRIMARY key: PRIMARY key\_len: 11 ref: ecommerce.sbtshrink.duns\_id rows: 1 Extra: Using where 4 rows in set (0.00 sec) I cant think of any additional information I might provide, but if there is anything, Please don’t hesitate to ask. I am getting an impression that there is no workaround for this aside from actually editing the user’s queries (which will be difficult as we lack a list of users who use third party tools such as crystal reports) Please, if anyone can help, I am out of ideas. Thanks. ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [January 29, 2008, 9:13am UTC](https://forums.percona.com/t/poor-performance-on-joins-after-mysql-4-5-migration/613/2 "2008-01-29T09:13:39Z")
Have you tried ANALYZE TABLE [yourtablehere] after importing the data to the new servers? It could help the optimizer to choose a better join order for you. ---
**Author:** ![mccormack\_adam](https://avatars.discourse-cdn.com/v4/letter/m/ac8455/32.png) [@mccormack\_adam](https://forums.percona.com/u/mccormack_adam)\ **Post date:** [January 29, 2008, 11:05am UTC](https://forums.percona.com/t/poor-performance-on-joins-after-mysql-4-5-migration/613/3 "2008-01-29T11:05:31Z")
And there was much rejoicing! That did the trick! And I am feeling VERY stupid for not thinking of it myself. Ah well, marked up as a learning experience. ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [January 30, 2008, 3:53am UTC](https://forums.percona.com/t/poor-performance-on-joins-after-mysql-4-5-migration/613/4 "2008-01-30T03:53:00Z")
Good! Don’t feel bad about not thinking about it, we have all done that at one time or another! Just remember it the next time! 😉