MySQL Forums
Forum List  »  Other Migration

Re: Migration from 4.1.7 to 5.0.27 queries taking 30 times as long.
Posted by: Adam McCormack
Date: January 28, 2008 09:55AM

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:
with straight join

*************************** 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)

Please, if anyone can help, I am out of ideas.
Thanks.



Edited 1 time(s). Last edit at 01/28/2008 09:57AM by Adam McCormack.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Migration from 4.1.7 to 5.0.27 queries taking 30 times as long.
2830
January 28, 2008 09:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.