SQL slowness when doing ActiveRecord find on a HABTM relationship

I'm running Rails 2.3.5 with a MySQL database. I have a HABTM relationship between Books and Users and am trying to get all users who have a specified list of books (defined by an array of book names).

I'm able to perform a find call that retrieves this list of users:

User.find(
  :all,
  :joins      => :books,
  :conditions => { :books => { :name => book_names } }
)

However, this turns out to be extremely slow. After playing around in SQL, I found that the following call works much faster and retrieves the same results:

User.find_by_sql([
  "SELECT users.* FROM users
   INNER JOIN books_users ON users.id = books_users.user_id
   WHERE books_users.book_id IN (SELECT id FROM books WHERE books.name IN (?))",
  book_names
])

For the same query, the find call takes roughly 3000 ms on my computer whereas the find_by_sql call takes roughly 200 ms; this is an entire magnitude of speed difference. I suspect the culprit has something to do with the fact that the original find call is translated into a double INNER JOIN SQL query, equivalent to the following:

[
  "SELECT users.* FROM users
   INNER 开发者_C百科JOIN books_users ON users.id = books_users.user_id
   INNER JOIN books ON books_users.book_id = books.id
   WHERE books.name IN (?)",
  book_names
]

My questions are:

• Does anyone know why this is the case? Why is the double INNER JOIN slower than my single INNER JOIN with a nested SELECT query?
• The find_by_sql call is not really taking advantage of the built-in support that Rails provides for HABTM relationships. In particular, it's surfacing the books_users join table that the Rails support typically abstracts away from the developer. Is there a way to specify the same query using a find call that hides this?

---

After the comments above, it sounds like you need indexes on your book_id and user_id fields in books_users.

class AddIndices < ActiveRecord::Migration
  def self.up
    add_index :books_users, :book_id
    add_index :books_users, :user_id
  end

  def self.down
    remove_index :books_users, :book_id
    remove_index :books_users, :user_id
  end
end

---

Does using :include vs. :join do the join better?

User.find(
  :all,
  :include    => :books,
  :conditions => { :books => { :name => book_names } }
)