Re: Migration from 4.1.7 to 5.0.27 queries taking 30 times as long.
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.