|
我有一个有5或6个LEFT JOINS的mysql查询.正如预期的那样,这很慢.考虑到我只期望大约100个结果,我可能更有意义的是运行大量单独的SQL查询并手动将它们拼接在一起.我猜这需要很长时间,因为使用多个左连接创建的巨大表.是对的吗？
我在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",:name => "index_person_redirect_relationships_on_start_person"
add_index "person_redirect_relationships",: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",:name => "index_organization_redirect_relationships_on_child_id"
add_index "organization_redirect_relationships",: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"
此查询未产生任何结果.
+—-+————-+———————————–+——–+———————————————————————————————————————-+——————————————————-+———+————————————————————————+——+———————————+ | 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之前弄清楚我是否有好的查询.
最佳答案
这么多JOIN可能是一个非常糟糕的主意,但你应该先显示你的查询.
首先,需要索引来加速查询.如果你没有,你可能应该创建一些(取决于你执行的查询).
如果你做了多个LEFT JOIN,那么你可以(可能)将它们分成不同的查询,这应该可以使应用程序更快地运行.
您可以参考MySQL’s documentation on optimization,特别是LEFT JOIN optimization和optimization using indexes.这可能会为您提供更多详细信息.
（编辑：常州站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
