Tips for MySQLsubquery in Rails3

Blog. rocodev. When the composts13-mysql-subquery-in-rails-3 sometimes configures RailsApplication, some JOIN statements are not that good. (Whether it's a hand-engraved image or an ORM .) Therefore, in Application explain (PHPorRails), two SELECT statements or subquery statements are usually used.

When the http://blog.rocodev.com/posts/13-mysql-subquery-in-rails-3 was used to design Rails Applications, some JOIN statements were not that good. (Whether it's a hand-engraved image or an ORM .) Therefore, in Application compute (PHP or Rails), two SELECT statements or subquery statements are usually used.

Http://blog.rocodev.com/posts/13-mysql-subquery-in-rails-3

Sometimes when designing Rails Applications, some JOIN statements are not that good. (Whether it's a hand-engraved image or an ORM .)

Therefore, two SELECT statements in Application compute (PHP or Rails) or subquery statements may change to a direct replacement solution.

For example, there is a function called: the total number of facvorite users on the racks I shelved.

Generally, the producer generates this code without the need for JOIN operations.

  book_ids = Book.where(:user_id => user.id)  favorite_count = Favorite.where(:book_id => book_ids).select("book_id").map(&:book_id).uniq.size

Because you cannot know how book_ids pulls the query, you have to pull it to the Application queue and then plug it back to use IN queue. A small amount of data .... But... When book_ids is used more frequently, this action takes effect. In addition to being slow, it also creates a pile of useless objects to refresh the space...

In contrast, Arel with a large backend in Rails 3 provides the dynamic subquery calculation method:

In fact, the above section can be changed to this category.

  favorite_count = Favorite.where(:book_id => Book.where(:user_id => user.id)).select("distinct book_id").count

Yes. In the query sentence, the next condition will automatically change to subquery ....

Favorite.where(:book_id => Book.where(:user_id => user.id))

Http://stackoverflow.com/questions/5483407/subqueries-in-activerecord

Several principles

• JOIN or slow
• The two SELECT statements are not necessarily faster than JOIN statements. (In the case where the number of book_id numbers is large)
• Two SELECT statements may be faster than JOIN statements. (In the case where the number of book_id numbers is small)
• In some cases, SUBQUERY should be used to replace two SELECT statements.
• SUBQUERY is not mongoshidan, because most of the time JOIN queries are more efficient than SUBQUERY, but SUBQUERY is better than SUBQUERY. You can skip this step...
• SUBQUERY performance can be stopped, too many SUBQUERY performance is very slow (two queries are actually slow ...).

Original article address: Tips for MySQL subquery in Rails 3. Thank you for sharing them with me.