我有一个有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之前弄清楚我是否有好的查询.