Home » Performance » MySQL » Poor performance on joins after MYSQL 4 -> 5 migration
| Poor performance on joins after MYSQL 4 -> 5 migration [message #2553] |
Mon, 28 January 2008 15:08  |
mccormack_adam
Messages: 2
Registered: January 2008 |
Junior Member |
|
|
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.
|
|
| |
| Re: Poor performance on joins after MYSQL 4 -> 5 migration [message #2556 is a reply to message #2553 ] |
Tue, 29 January 2008 11:35  |
mccormack_adam
Messages: 2
Registered: January 2008 |
Junior Member |
|
|
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.
|
|
| |
Goto Forum:
Current Time: Sun Jul 5 18:00:04 EDT 2009
Total time taken to generate the page: 0.06690 seconds |
Members
Search
Help
Register
Login
Home
