Description:
For complex queries like this:
SELECT path_idx, path_idx, margin, length, path_delay, skew, paths.type_code,
CONCAT_WS(' ',ROUND(seg1.delay,3),CONCAT('A',CASE seg1.event_reference_clock_trans WHEN
'H2L' THEN 'F' WHEN 'Z2L' THEN 'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H' THEN 'R' ELSE
seg1.event_reference_clock_trans END),clk1.name),
CONCAT_WS(' ',ROUND(seg2.delay,3),CONCAT('A',CASE seg2.event_reference_clock_trans WHEN
'H2L' THEN 'F' WHEN 'Z2L' THEN 'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H' THEN 'R' ELSE
seg2.event_reference_clock_trans END),clk2.name),
CONCAT_WS(' ',ROUND(check_value - clk_delay,3),CONCAT('B',CASE clk_tran WHEN 'H2L' THEN
'F' WHEN 'Z2L' THEN 'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H' THEN 'R' ELSE clk_tran
END),clk3.name),
CASE IFNULL(last_clocked_pin_trans,start_pin_trans) WHEN 'H2L' THEN 'F' WHEN 'Z2L' THEN
'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H' THEN 'R' ELSE
IFNULL(last_clocked_pin_trans,start_pin_trans) END,
p1.name,
CASE end_pin_trans WHEN 'H2L' THEN 'F' WHEN 'Z2L' THEN 'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H'
THEN 'R' ELSE end_pin_trans END,
p2.name, IFNULL(n3.name,'evr'), IFNULL(t1.name,'evr'), IFNULL(t2.name,'evr')
FROM paths, pins as p1, pins as p2, nets as n1, nets as n2,
path_segments as seg1, path_segments as seg2, clocks as clk1, clocks as clk2,
checks, clocks as clk3, timing_nodes, nets as n3, cells as c1,
cells as c2, cell_templates as t1, cell_templates as t2
WHERE paths.mode='l'
AND path_idx IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23) AND
IFNULL(last_clocked_pin_idx,start_pin_idx)=p1.pin_idx
AND end_pin_idx=p2.pin_idx
AND IFNULL(last_clocked_pin_net_idx,start_pin_net_idx)=n1.net_idx
AND end_pin_net_idx=n2.net_idx
AND IFNULL(last_clocked_seg_idx,start_seg_idx)=seg1.seg_idx
AND end_seg_idx=seg2.seg_idx
AND paths.check_idx=checks.check_idx
AND seg1.event_reference_clock_idx=clk1.clock_idx
AND seg2.event_reference_clock_idx=clk2.clock_idx
AND clk_idx=clk3.clock_idx
AND ref_node_idx=timing_nodes.node_idx
AND timing_nodes.net_idx=n3.net_idx
AND p1.owner_cell_idx=c1.cell_idx
AND c1.owner_lib_temp_idx=t1.template_idx
AND p2.owner_cell_idx=c2.cell_idx
AND c2.owner_lib_temp_idx=t2.template_idx
ORDER BY (path_idx)
optimizer of MySQL 5.1 produce an inefficient execution plan:
1 SIMPLE clk1 ALL PRIMARY {} {} {} 2 {Using temporary; Using filesort}
1 SIMPLE clk2 ALL PRIMARY {} {} {} 2 {Using join buffer}
1 SIMPLE clk3 ALL PRIMARY {} {} {} 2 {Using join buffer}
1 SIMPLE paths ALL PRIMARY,check_idx,end_seg_idx,end_pin_idx,end_pin_net_idx {} {} {} 23
{Using where; Using join buffer}
1 SIMPLE n1 eq_ref PRIMARY PRIMARY 4 func 1 {Using where; Using index}
1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 timing_db.paths.end_pin_net_idx 1 {Using index}
1 SIMPLE checks eq_ref PRIMARY,clk_idx,FK_CHECKS_REF_NODE_IDX PRIMARY 4
timing_db.paths.check_idx 1 {Using where}
1 SIMPLE timing_nodes eq_ref PRIMARY,net_idx PRIMARY 4 timing_db.checks.ref_node_idx 1
{}
1 SIMPLE n3 eq_ref PRIMARY PRIMARY 4 timing_db.timing_nodes.net_idx 1 {}
1 SIMPLE p1 eq_ref PRIMARY,FK_PINS_OWNER_CELL_IDX PRIMARY 4 func 1 {Using where}
1 SIMPLE c1 eq_ref PRIMARY PRIMARY 4 timing_db.p1.owner_cell_idx 1 {}
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 timing_db.c1.owner_lib_temp_idx 1 {Using where}
1 SIMPLE p2 eq_ref PRIMARY,FK_PINS_OWNER_CELL_IDX PRIMARY 4 timing_db.paths.end_pin_idx 1
{}
1 SIMPLE c2 eq_ref PRIMARY PRIMARY 4 timing_db.p2.owner_cell_idx 1 {}
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 timing_db.c2.owner_lib_temp_idx 1 {Using where}
1 SIMPLE seg1 eq_ref PRIMARY,event_reference_clock_idx PRIMARY 4 func 1 {Using where}
1 SIMPLE seg2 eq_ref PRIMARY,event_reference_clock_idx PRIMARY 4
timing_db.paths.end_seg_idx 1 {Using where}
The problem is not only in accessing entire paths table 8 times instead of one. Query is
executed dozens times slower than on 5.0.x with the following plan:
1 SIMPLE paths ALL PRIMARY,check_idx,end_seg_idx,end_pin_idx,end_pin_net_idx {} {} {} 23
{Using where; Using filesort}
1 SIMPLE n1 eq_ref PRIMARY PRIMARY 4 func 1 {Using where; Using index}
1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 timing_db.paths.end_pin_net_idx 1 {Using index}
1 SIMPLE checks eq_ref PRIMARY,clk_idx,FK_CHECKS_REF_NODE_IDX PRIMARY 4
timing_db.paths.check_idx 1 {}
1 SIMPLE clk3 eq_ref PRIMARY PRIMARY 4 timing_db.checks.clk_idx 1 {}
1 SIMPLE timing_nodes eq_ref PRIMARY,net_idx PRIMARY 4 timing_db.checks.ref_node_idx 1
{}
1 SIMPLE n3 eq_ref PRIMARY PRIMARY 4 timing_db.timing_nodes.net_idx 1 {}
1 SIMPLE p1 eq_ref PRIMARY,FK_PINS_OWNER_CELL_IDX PRIMARY 4 func 1 {Using where}
1 SIMPLE c1 eq_ref PRIMARY PRIMARY 4 timing_db.p1.owner_cell_idx 1 {}
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 timing_db.c1.owner_lib_temp_idx 1 {Using where}
1 SIMPLE p2 eq_ref PRIMARY,FK_PINS_OWNER_CELL_IDX PRIMARY 4 timing_db.paths.end_pin_idx 1
{}
1 SIMPLE c2 eq_ref PRIMARY PRIMARY 4 timing_db.p2.owner_cell_idx 1 {}
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 timing_db.c2.owner_lib_temp_idx 1 {Using where}
1 SIMPLE seg1 eq_ref PRIMARY,event_reference_clock_idx PRIMARY 4 func 1 {Using where}
1 SIMPLE clk1 eq_ref PRIMARY PRIMARY 4 timing_db.seg1.event_reference_clock_idx 1 {}
1 SIMPLE seg2 eq_ref PRIMARY,event_reference_clock_idx PRIMARY 4
timing_db.paths.end_seg_idx 1 {}
1 SIMPLE clk2 eq_ref PRIMARY PRIMARY 4 timing_db.seg2.event_reference_clock_idx 1 {}
(6 sec vs. 0.13, for example), and most of that time is spent on optimization (as EXPLAIN
alone works for 6 seconds). This is a serious performance degradation that prevents
upgrade.
All tables are MyISAM. ANALYZE/OPTIMIZE TABLE for all of them does not help. Query
returns only 23 rows.
The only way to force proper execution plan is to force exact join order with
STRAIGHT_JOINs.
How to repeat:
Run the query on tables provided (see private comment).
Suggested fix:
Make optimizer in 5.1 to work in the same way as in 5.0. Actually, there is a simple
workaround:
set session optimizer_search_depth=0;
With this setting exactly the same plan as in 5.0 is produced almost instantly, and the
query is executed really fast. So, maybe 0 should be used by default, not 62.
Description: For complex queries like this: SELECT path_idx, path_idx, margin, length, path_delay, skew, paths.type_code, CONCAT_WS(' ',ROUND(seg1.delay,3),CONCAT('A',CASE seg1.event_reference_clock_trans WHEN 'H2L' THEN 'F' WHEN 'Z2L' THEN 'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H' THEN 'R' ELSE seg1.event_reference_clock_trans END),clk1.name), CONCAT_WS(' ',ROUND(seg2.delay,3),CONCAT('A',CASE seg2.event_reference_clock_trans WHEN 'H2L' THEN 'F' WHEN 'Z2L' THEN 'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H' THEN 'R' ELSE seg2.event_reference_clock_trans END),clk2.name), CONCAT_WS(' ',ROUND(check_value - clk_delay,3),CONCAT('B',CASE clk_tran WHEN 'H2L' THEN 'F' WHEN 'Z2L' THEN 'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H' THEN 'R' ELSE clk_tran END),clk3.name), CASE IFNULL(last_clocked_pin_trans,start_pin_trans) WHEN 'H2L' THEN 'F' WHEN 'Z2L' THEN 'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H' THEN 'R' ELSE IFNULL(last_clocked_pin_trans,start_pin_trans) END, p1.name, CASE end_pin_trans WHEN 'H2L' THEN 'F' WHEN 'Z2L' THEN 'F' WHEN 'L2H' THEN 'R' WHEN 'Z2H' THEN 'R' ELSE end_pin_trans END, p2.name, IFNULL(n3.name,'evr'), IFNULL(t1.name,'evr'), IFNULL(t2.name,'evr') FROM paths, pins as p1, pins as p2, nets as n1, nets as n2, path_segments as seg1, path_segments as seg2, clocks as clk1, clocks as clk2, checks, clocks as clk3, timing_nodes, nets as n3, cells as c1, cells as c2, cell_templates as t1, cell_templates as t2 WHERE paths.mode='l' AND path_idx IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23) AND IFNULL(last_clocked_pin_idx,start_pin_idx)=p1.pin_idx AND end_pin_idx=p2.pin_idx AND IFNULL(last_clocked_pin_net_idx,start_pin_net_idx)=n1.net_idx AND end_pin_net_idx=n2.net_idx AND IFNULL(last_clocked_seg_idx,start_seg_idx)=seg1.seg_idx AND end_seg_idx=seg2.seg_idx AND paths.check_idx=checks.check_idx AND seg1.event_reference_clock_idx=clk1.clock_idx AND seg2.event_reference_clock_idx=clk2.clock_idx AND clk_idx=clk3.clock_idx AND ref_node_idx=timing_nodes.node_idx AND timing_nodes.net_idx=n3.net_idx AND p1.owner_cell_idx=c1.cell_idx AND c1.owner_lib_temp_idx=t1.template_idx AND p2.owner_cell_idx=c2.cell_idx AND c2.owner_lib_temp_idx=t2.template_idx ORDER BY (path_idx) optimizer of MySQL 5.1 produce an inefficient execution plan: 1 SIMPLE clk1 ALL PRIMARY {} {} {} 2 {Using temporary; Using filesort} 1 SIMPLE clk2 ALL PRIMARY {} {} {} 2 {Using join buffer} 1 SIMPLE clk3 ALL PRIMARY {} {} {} 2 {Using join buffer} 1 SIMPLE paths ALL PRIMARY,check_idx,end_seg_idx,end_pin_idx,end_pin_net_idx {} {} {} 23 {Using where; Using join buffer} 1 SIMPLE n1 eq_ref PRIMARY PRIMARY 4 func 1 {Using where; Using index} 1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 timing_db.paths.end_pin_net_idx 1 {Using index} 1 SIMPLE checks eq_ref PRIMARY,clk_idx,FK_CHECKS_REF_NODE_IDX PRIMARY 4 timing_db.paths.check_idx 1 {Using where} 1 SIMPLE timing_nodes eq_ref PRIMARY,net_idx PRIMARY 4 timing_db.checks.ref_node_idx 1 {} 1 SIMPLE n3 eq_ref PRIMARY PRIMARY 4 timing_db.timing_nodes.net_idx 1 {} 1 SIMPLE p1 eq_ref PRIMARY,FK_PINS_OWNER_CELL_IDX PRIMARY 4 func 1 {Using where} 1 SIMPLE c1 eq_ref PRIMARY PRIMARY 4 timing_db.p1.owner_cell_idx 1 {} 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 timing_db.c1.owner_lib_temp_idx 1 {Using where} 1 SIMPLE p2 eq_ref PRIMARY,FK_PINS_OWNER_CELL_IDX PRIMARY 4 timing_db.paths.end_pin_idx 1 {} 1 SIMPLE c2 eq_ref PRIMARY PRIMARY 4 timing_db.p2.owner_cell_idx 1 {} 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 timing_db.c2.owner_lib_temp_idx 1 {Using where} 1 SIMPLE seg1 eq_ref PRIMARY,event_reference_clock_idx PRIMARY 4 func 1 {Using where} 1 SIMPLE seg2 eq_ref PRIMARY,event_reference_clock_idx PRIMARY 4 timing_db.paths.end_seg_idx 1 {Using where} The problem is not only in accessing entire paths table 8 times instead of one. Query is executed dozens times slower than on 5.0.x with the following plan: 1 SIMPLE paths ALL PRIMARY,check_idx,end_seg_idx,end_pin_idx,end_pin_net_idx {} {} {} 23 {Using where; Using filesort} 1 SIMPLE n1 eq_ref PRIMARY PRIMARY 4 func 1 {Using where; Using index} 1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 timing_db.paths.end_pin_net_idx 1 {Using index} 1 SIMPLE checks eq_ref PRIMARY,clk_idx,FK_CHECKS_REF_NODE_IDX PRIMARY 4 timing_db.paths.check_idx 1 {} 1 SIMPLE clk3 eq_ref PRIMARY PRIMARY 4 timing_db.checks.clk_idx 1 {} 1 SIMPLE timing_nodes eq_ref PRIMARY,net_idx PRIMARY 4 timing_db.checks.ref_node_idx 1 {} 1 SIMPLE n3 eq_ref PRIMARY PRIMARY 4 timing_db.timing_nodes.net_idx 1 {} 1 SIMPLE p1 eq_ref PRIMARY,FK_PINS_OWNER_CELL_IDX PRIMARY 4 func 1 {Using where} 1 SIMPLE c1 eq_ref PRIMARY PRIMARY 4 timing_db.p1.owner_cell_idx 1 {} 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 timing_db.c1.owner_lib_temp_idx 1 {Using where} 1 SIMPLE p2 eq_ref PRIMARY,FK_PINS_OWNER_CELL_IDX PRIMARY 4 timing_db.paths.end_pin_idx 1 {} 1 SIMPLE c2 eq_ref PRIMARY PRIMARY 4 timing_db.p2.owner_cell_idx 1 {} 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 timing_db.c2.owner_lib_temp_idx 1 {Using where} 1 SIMPLE seg1 eq_ref PRIMARY,event_reference_clock_idx PRIMARY 4 func 1 {Using where} 1 SIMPLE clk1 eq_ref PRIMARY PRIMARY 4 timing_db.seg1.event_reference_clock_idx 1 {} 1 SIMPLE seg2 eq_ref PRIMARY,event_reference_clock_idx PRIMARY 4 timing_db.paths.end_seg_idx 1 {} 1 SIMPLE clk2 eq_ref PRIMARY PRIMARY 4 timing_db.seg2.event_reference_clock_idx 1 {} (6 sec vs. 0.13, for example), and most of that time is spent on optimization (as EXPLAIN alone works for 6 seconds). This is a serious performance degradation that prevents upgrade. All tables are MyISAM. ANALYZE/OPTIMIZE TABLE for all of them does not help. Query returns only 23 rows. The only way to force proper execution plan is to force exact join order with STRAIGHT_JOINs. How to repeat: Run the query on tables provided (see private comment). Suggested fix: Make optimizer in 5.1 to work in the same way as in 5.0. Actually, there is a simple workaround: set session optimizer_search_depth=0; With this setting exactly the same plan as in 5.0 is produced almost instantly, and the query is executed really fast. So, maybe 0 should be used by default, not 62.