When using EXPLAIN to find the finer details of what is going on under the hood, you will surely come across this,
Using where; Using temporary; Using filesort
or some combination like that. The important things to notice here are
temporary requires carefully defining indices, more on that subject can be found in the MySQL docs.
filesort, (especially when your SQL is abstracted by something like ActiveRecord) can be a little more tricky. The problem comes from using ` … :group => “[column]” ` in our scope, for example.
Booking.all( :select => "count(1) as bookings_per_day, date(created_at) as booking_date", :conditions => [ "user_id = ? and status = 'accepted' and pending = 0 and created_at between ? and ?", @user.id, extract_date(dates.first.beginning_of_week), extract_date(dates.last) ], :group => "booking_date")
The SQL query derived from this code results in a ‘filesort’ opertation, something we want to avoid. MySQL docs state:
By default, MySQL sorts all GROUP BY col1, col2, … queries as if you specified ORDER BY col1, col2, … in the query as well. If you include an ORDER BY clause explicitly that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL.
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
So let’s go back to the code and add that in.
Booking.all( :select => "count(1) as bookings_per_day, date(created_at) as booking_date", :conditions => [ .. etc .. ], :group => "booking_date", :order => "null")
Note that it isn’t
:order => nil. This results in the order not being included in the result SQL at all.
The EXPLAIN now gives us
Using where; Using temporary. Chances are, the order implied by the
:group is what you want anyway. Of course, if you were sorting by a column explicitly you would care about the order, in which case you would be stating this in the query. It’s almost as if ActiveRecord should know to add this if there is no
:order specified when using
For more detail on exactly what MySQL is doing when filesorting, take a look at this detailed article, How MySQL executes ORDER BY