我正在Rails 3中进行此操作。我知道创建活动记录很昂贵,但我认为这可能比有太多LEFT JOINS要快。我对数据库在幕后的工作知之甚少。非常感谢任何见解。
编辑:这是实际查询和表模式
查询:
SELECT people.* FROM people LEFT JOIN person_organization_relationships ON person_organization_relationships.person_id = people.id AND person_organization_relationships.stop_person IS NULL LEFT JOIN person_redirect_relationships AS r_from_others ON r_from_others.parent_id = people.id AND r_from_others.stop_person IS NULL LEFT JOIN person_redirect_relationships AS r_to_others ON r_to_others.child_id = people.id AND r_to_others.stop_person IS NULL LEFT JOIN person_organization_relationships AS r_p_check ON r_p_check.person_id = r_from_others.child_id AND r_p_check.stop_person IS NULL LEFT JOIN organization_redirect_relationships AS r_o_check ON r_o_check.child_id = person_organization_relationships.organization_id AND r_o_check.stop_organization IS NULL LEFT JOIN person_organization_relationships AS rr_p_check ON rr_p_check.person_id = r_from_others.child_id AND rr_p_check.stop_person IS NULL LEFT JOIN organization_redirect_relationships AS rr_o_check ON rr_p_check.organization_id = rr_o_check.child_id AND rr_o_check.stop_organization IS NULL WHERE (((person_organization_relationships.organization_id = 1 OR r_o_check.parent_id = 1) AND r_to_others.parent_id IS NULL) OR (r_p_check.organization_id = 1 OR rr_o_check.parent_id = 1)) GROUP BY people.id
表模式:
create_table "people", :force => true do |t|
t.datetime "created_at"
t.datetime "updated_at"
t.boolean "delta", :default => true, :null => false
end
create_table "person_organization_relationships", :force => true do |t|
t.integer "person_id"
t.integer "organization_id"
t.integer "start_person"
t.integer "stop_person"
t.datetime "created_at"
t.datetime "updated_at"
end
add_index "person_organization_relationships", ["organization_id"], :name => "index_person_organization_relationships_on_organization_id"
add_index "person_organization_relationships", ["person_id"], :name => "index_person_organization_relationships_on_person_id"
add_index "person_organization_relationships", ["start_person"], :name => "index_person_organization_relationships_on_start_person"
add_index "person_organization_relationships", ["stop_person"], :name => "index_person_organization_relationships_on_stop_person"
create_table "person_redirect_relationships", :force => true do |t|
t.integer "parent_id"
t.integer "child_id"
t.integer "start_person"
t.integer "stop_person"
t.datetime "created_at"
t.datetime "updated_at"
end
add_index "person_redirect_relationships", ["child_id"], :name => "index_person_redirect_relationships_on_child_id"
add_index "person_redirect_relationships", ["parent_id"], :name => "index_person_redirect_relationships_on_parent_id"
add_index "person_redirect_relationships", ["start_person"], :name => "index_person_redirect_relationships_on_start_person"
add_index "person_redirect_relationships", ["stop_person"], :name => "index_person_redirect_relationships_on_stop_person"
create_table "organization_redirect_relationships", :force => true do |t|
t.integer "parent_id"
t.integer "child_id"
t.integer "start_organization"
t.integer "stop_organization"
t.datetime "created_at"
t.datetime "updated_at"
end
add_index "organization_redirect_relationships", ["child_id"], :name => "index_organization_redirect_relationships_on_child_id"
add_index "organization_redirect_relationships", ["parent_id"], :name => "index_organization_redirect_relationships_on_parent_id"
add_index "organization_redirect_relationships", ["start_organization"], :name => "index_organization_redirect_relationships_on_start_organization"
add_index "organization_redirect_relationships", ["stop_organization"], :name => "index_organization_redirect_relationships_on_stop_organization"
这个查询没有返回任何结果。
注:此SQL查询结果包含多个表格的信息,用于优化查询性能。+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra |+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+
| 1 | SIMPLE | person_details | ALL | index_person_details_on_current_p_id
| NULL | NULL | NULL
| 4938 | Using temporary; Using filesort || 1 | SIMPLE | people | eq_ref | PRIMARY
| PRIMARY | 4 | knolcano_development.person_details.current_p_id
| 1 | || 1 | SIMPLE | person_organization_relationships | ref | index_person_organization_relationships_on_person_id,index_person_organization_relationships_on_stop_person | index_person_organization_relationships_on_person_id | 5 | knolcano_development.person_details.current_p_id
| 1 | || 1 | SIMPLE | r_from_others | ref | index_person_redirect_relationships_on_parent_id,index_person_redirect_relationships_on_stop_person | index_person_redirect_relationships_on_stop_person | 5 | const
| 3 | || 1 | SIMPLE | r_to_others | ref | index_person_redirect_relationships_on_child_id,index_person_redirect_relationships_on_stop_person | index_person_redirect_relationships_on_child_id | 5 | knolcano_development.people.id
| 2 | || 1 | SIMPLE | r_p_check | ref | index_person_organization_relationships_on_person_id,index_person_organization_relationships_on_stop_person | index_person_organization_relationships_on_person_id | 5 | knolcano_development.r_from_others.child_id
| 1 | || 1 | SIMPLE | r_o_check | ref | index_organization_redirect_relationships_on_child_id,index_organization_redirect_relationships_on_stop_organization | index_organization_redirect_relationships_on_child_id | 5 | knolcano_development.person_organization_relationships.organization_id | 1 | |
| 1 | SIMPLE | rr_p_check | ref | index_person_organization_relationships_on_person_id,index_person_organization_relationships_on_stop_person | index_person_organization_relationships_on_person_id | 5 | knolcano_development.r_from_others.child_id
| 1 | || 1 | SIMPLE | rr_o_check | ref | index_organization_redirect_relationships_on_child_id,index_organization_redirect_relationships_on_stop_organization | index_organization_redirect_relationships_on_child_id | 5 | knolcano_development.rr_p_check.organization_id
| 1 | Using where |+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+
9 rows in set (0.00 sec)
但是当我运行查询时,它花费了0.14秒。这算是很长的时间吗?我正在尝试确定在实现memcached之前是否有良好的查询。
EXPLAIN
的查询和结果。 - Tadeck