Improve performance with LEFT JOIN

Question

I have a mysql query that has 5 or 6 LEFT JOINS. As expected, this is pretty slow. Considering that I'm only expecting ~100 results, might it make more sense for me to run lots of separate sql queries and manually stitch them together. I'm guessing that it's taking a long time because of the huge table that is created with multiple left joins. Is that right?

I'm doing this in Rails 3. I know that creating active records is expensive, but I'm figuring it may be faster than having so many LEFT JOINS. I know very little about how the database works under the hood. Would really appreciate any insights.

---

Edit: Here's the actual query and table schemas

Query

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

Table schema:

  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"

---

This query yielded no results.

+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+ | 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)

But when I ran the query it took 0.14 sec. Is that a long period of time? I'm trying to figure out if I have good queries before I implement memcached.

Answer 1

So many JOINs may be a very bad idea, but you should show your query first.

First of all, indexes are required to speed up the query. If you do not have any, you probably should create some (depending on the query you perform).

And if you do multiple LEFT JOINs, then you could (probably) separate them into different queries and this should make the application work a lot faster.

You can refer to the MySQL's documentation on optimization, specifically LEFT JOIN optimization and optimization using indexes. This may give you additional details.

Answer 2

There could be a number of reasons for this. Poor query performance, bad indexes, and so on. The explain, query, and probably even create table statements for the tables in question would go a very long way to helping come to an answer.

Most of the time when I see this kind of thing, though, it is a matter of poor indexes.