What's snewinEdgeRails: EXPLAIN

Source: Internet
Author: User
WhatsnewinEdgeRails: EXPLAIN: Unknown: RunningEXPLAINmanuallyAutomaticEXPLAINforslowqueriesSilencingautomaticEXPLAINAsofthisw

What's new in Edge Rails: EXPLAIN: There are some new features related to EXPLAIN in the forthcoming Ruby on Rails 3.2 we 'd like to share: running EXPLAIN manually Automatic EXPLAIN for slow queries Silencing automatic EXPLAIN As of this w

What's new in Edge Rails: EXPLAIN:
There are some new features related to EXPLAIN in the forthcoming Ruby on Rails 3.2 we 'd like
To share:

  • Running EXPLAIN manually
  • Automatic EXPLAIN for slow queries
  • Silencing automatic EXPLAIN
As of this writing they are available for the adapters sqlite3, mysql2, And postgresql.
Running EXPLAIN Manually

You can now run EXPLAIN on the SQL generated by a relation this way:

User.where(:id => 1).joins(:posts).explain
The result of that method call is a string that carefully imitates the output of database shells.


Please note thatexplain RunsThe query or queries and asks the database for their respective query plan afterwards. this is because due to eager loading a relation may trigger several queries to fetch the records and their associations, and in such cases some queries need the result of the previous ones.


If the relation triggers several queries the method still returns a single string with all the query plans. this is an output meant for human consumption so we preferred to present everything as a string in a format which is familiar right away rather than a structure.


Automatic EXPLAIN For Slow Queries

Rails 3.2 has the ability to help in detecting slow queries.

New applications get

config.active_record.auto_explain_threshold_in_seconds = 0.5

InConfig/environments/development. rb. Active Record monitors queries and if they take more than that threshold their query plan will be logged usingwarn.


That works for anything runningfind_by_sql(Which is almost everything, since most of Active Record ends up calling that method ). in the participates case of relations, the threshold is compared against the total time needed to fetch the records, not against the time taken by each inpidual query. because conceptually we are concerned with the cost of the call

User.where(:id => 1).joins(:posts).explain

Rather than the cost of the different queries that call may trigger due to the implementation.

By default the threshold isnilIn the test and production environments, which means the feature is disabled.


The value of that parameter isnilAlso if the threshold is not set, so existing applications will need to add it by hand if they migrate to 3.2 to be able to enable automatic EXPLAIN.

Silencing Automatic EXPLAIN

With automatic EXPLAIN enabled, it cocould still be the case that some queries are just slow and you know they have to be. For example, a heavyweight report in the backoffice.

The macrosilence_auto_explainAllows you to avoid having EXPLAIN run repeatedly in those areas of code:


ActiveRecord::Base.silence_auto_explain do # no automatic EXPLAIN hereend
Interpreting Query PlansThe interpretation of the query plans is another topic, these are some pointers:
  • SQLite: EXPLAIN QUERY PLAN
  • MySQL: EXPLAIN Output Format
  • PostgreSQL: Using EXPLAIN
Happy debugging!

Original article address: What's new in Edge Rails: EXPLAIN. Thank you for sharing it with me.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.