From 18453600aa5c208276c7d2f329952642924f4a75 Mon Sep 17 00:00:00 2001 From: Tom Hughes Date: Fri, 31 Aug 2007 11:14:40 +0000 Subject: [PATCH] Split the createuniquenodes query in two as MySQL was choosing a very bad execution plan for it with the OR condition. --- app/controllers/amf_controller.rb | 39 +++++++++++++++++++++---------- 1 file changed, 27 insertions(+), 12 deletions(-) diff --git a/app/controllers/amf_controller.rb b/app/controllers/amf_controller.rb index 8db813b38..5771f4e3d 100644 --- a/app/controllers/amf_controller.rb +++ b/app/controllers/amf_controller.rb @@ -691,18 +691,33 @@ end def createuniquenodes(uqs_name,uqn_name) # Finds nodes which appear in uniquesegments but no other segments sql=<<-EOF - CREATE TEMPORARY TABLE #{uqn_name} - SELECT DISTINCT node_id - FROM (SELECT cn.id AS node_id - FROM current_nodes AS cn, - current_segments AS cs, - #{uqs_name} AS us - WHERE cs.id=us.segment_id - AND (cn.id=cs.node_a OR cn.id=cs.node_b)) AS n - LEFT JOIN current_segments AS cs2 ON node_id=cs2.node_a AND cs2.visible=1 - LEFT JOIN current_segments AS cs3 ON node_id=cs3.node_b AND cs3.visible=1 - WHERE cs2.node_a IS NULL - AND cs3.node_b IS NULL + CREATE TEMPORARY TABLE #{uqn_name} + SELECT DISTINCT node_id + FROM (SELECT cn.id AS node_id + FROM current_nodes AS cn, + current_segments AS cs, + #{uqs_name} AS us + WHERE cs.id=us.segment_id + AND cn.id=cs.node_a) AS n + LEFT JOIN current_segments AS cs2 ON node_id=cs2.node_a AND cs2.visible=1 + LEFT JOIN current_segments AS cs3 ON node_id=cs3.node_b AND cs3.visible=1 + WHERE cs2.node_a IS NULL + AND cs3.node_b IS NULL + EOF + ActiveRecord::Base.connection.execute(sql) + sql=<<-EOF + INSERT INTO #{uqn_name} + SELECT DISTINCT node_id + FROM (SELECT cn.id AS node_id + FROM current_nodes AS cn, + current_segments AS cs, + #{uqs_name} AS us + WHERE cs.id=us.segment_id + AND cn.id=cs.node_b) AS n + LEFT JOIN current_segments AS cs2 ON node_id=cs2.node_a AND cs2.visible=1 + LEFT JOIN current_segments AS cs3 ON node_id=cs3.node_b AND cs3.visible=1 + WHERE cs2.node_a IS NULL + AND cs3.node_b IS NULL EOF ActiveRecord::Base.connection.execute(sql) end -- 2.39.5