An execution plan describes a recommended method of execution for a SQL statement. The query optimizer attempts to generate the most optimal execution plan for a SQL statement.
The optimizer chooses the plan with the lowest cost in terms of I/O, CPU, and communication. The database gathers these statistics about our objects like the number of rows, distinct values, and data distribution. The optimizer has such internal statistics and tools at its disposal which are used to calculate the said cost and pick an execution plan.
The Optimizer Hints give us the opportunity to influence the optimizer. We, as an application architect, might know information about our data that the optimizer does not. And that knowledge, on some occasions, can help us to choose a more optimized execution plan than the optimizer.
Before Rails 6
To use Optimizer Hints before Rails 6, we had to use raw SQL queries.
In the example above, the
+ before the comment tells the database server that this is a hint.
FULL hint instructs that a full table scan should be performed on the students table.
Rails 6 adds support for setting optimizer hints on databases.
We can use ActiveRecord::QueryMethods#optimizer_hints to specify optimizer hints to be used in the SELECT statement.
In the example above, we can see that the query has our optimizer hints included.
Example from our Application
Let us discuss a live use-case of optimizer hints in one of our Rails App in production.
In one of our client’s projects, we were facing very long query times because we had to query a table with millions of records.
The solution was decided to be a timeout after 10 seconds on the user-facing query, and run the query in the background and cache for larger results.
It was implemented as below:
1. Using the
This example is based on work from our friends at Gumroad.
In the snippet above, we are using ruby’s
timeout method allows us to terminate any operation taking
longer than the specified duration.
This turned out to be a non-viable solution, as it stops the execution of the block, but does not stop the SQL query if it has already started, which was our intention, to begin with.
2. Using MySQL system variable
In the snippet above, we use the
to set the MySQL server system variable
For every query that is wrapped around
timeout_queries method call,
we set the value of
max_execution_time which results in query timing out
if its execution time reaches
We perform this extra overhead, so that we don’t have to rewrite all our existing queries with raw SQL.
Note: This only works on
Use Optimizer Hints with Rails 6 for timeout
The above problem can now be fixed using Optimizer Hints instead of writing out the timeout method. MySQL provides an optimizer hint named MAX_EXECUTION_TIME.
This limits the query to not be executed for more than the specified time limit N(in milliseconds).
This optimizer hint takes precedence over the value of the MySQL server system variable
Which means that even if there is a positive value of
already set, the execution time limit given in the optimizer hint would be honored.
Here’s how we can achieve the same in Rails 6:
In the snippet above, the query will be executed for a maximum of 10 seconds.
As we can see, this eliminates the need for setting the value of the MySQL server system variable
before every query as well as using raw SQL.
Note: This only works on
We saw what optimizer hints are and how we can use them in our queries. However, more often than not, we don’t require them. Modern-day databases do not encourage their use since with every upgrade, or a new patch, things are likely to go wrong.
However, we also saw a live example where using an optimizer hint, could help us achieve a particular behavior which was earlier done in a much more passive way.