Améliorer les performances avec une JOINTURE GAUCHE

J'ai une requête mysql qui a 5 ou 6 GAUCHE REJOINT. Comme prévu, c'est assez lent. Considérant que je suis le seul à attendre ~100 résultats, il pourrait faire plus de sens pour moi de courir beaucoup de séparer les requêtes sql manuellement et de les coudre ensemble. Je suppose que c'est en prenant un long moment à cause de l'énorme table est créée avec plusieurs gauche rejoint. Est ce que le droit?

Je fais cela dans Rails 3. Je sais que la création de dossiers actifs est cher, mais je suis en train de comprendre qu'il peut être plus rapide que d'avoir tant de GAUCHE REJOINT. Je sais très peu de choses sur la façon dont la base de données fonctionne sous le capot. Serait vraiment apprécier toutes les suggestions.

---

Edit: Voici la requête réelle et schémas de table

Requête

SÉLECTIONNEZ people.* DE people LEFT JOIN
person_organization_relationships SUR
person_organization_relationships.person_id = people.id ET
person_organization_relationships.stop_person EST NULL LEFT JOIN
person_redirect_relationships COMME r_from_others SUR
r_from_others.parent_id = people.id ET r_from_others.stop_person EST
NULL LEFT JOIN person_redirect_relationships COMME r_to_others SUR
r_to_others.child_id = people.id ET r_to_others.stop_person EST NULL
LEFT JOIN person_organization_relationships COMME r_p_check SUR
r_p_check.person_id = r_from_others.child_id ET r_p_check.stop_person
EST NULL LEFT JOIN organization_redirect_relationships COMME r_o_check
SUR r_o_check.child_id =
person_organization_relationships.organization_id ET
r_o_check.stop_organization EST NULL LEFT JOIN
person_organization_relationships COMME rr_p_check SUR
rr_p_check.person_id = r_from_others.child_id ET
rr_p_check.stop_person EST NULL LEFT JOIN
organization_redirect_relationships COMME rr_o_check SUR
rr_p_check.organization_id = rr_o_check.child_id ET
rr_o_check.stop_organization EST NULL OÙ
(((person_organization_relationships.organization_id = 1 OU
r_o_check.parent_id = 1) ET r_to_others.parent_id EST NUL) OU
(r_p_check.organization_id = 1 OU rr_o_check.parent_id = 1)) GROUP BY
people.id

Schéma de la Table:

  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"

---

Cette requête n'a donné aucun résultat.

+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+ | id | select_type | table | type |
possible_keys
| clé | key_len |
ref
| lignes | Extra |
+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+ | 1 | SIMPLE | person_details | TOUTES les |
index_person_details_on_current_p_id
| NULL | NULL |
NULL
| 4938 | Aide temporaire; à l'Aide de filesort | | 1 | SIMPLE | personnes
| eq_ref | PRIMAIRE
| PRIMAIRE | 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.personnes.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 | où |
+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+ 9 rows in set (0.00 sec)

Mais quand j'ai couru à la requête qu'il a pris 0.14 sec. Est qu'une longue période de temps? Je suis en train de voir si j'ai de bonnes questions avant que je les mettre en œuvre memcached.

OriginalL'auteur Cyrus | 2011-10-26