16

I have this ActiveRecord query 

issue = Issue.find(id)
issue.articles.includes(:category).merge(Category.where(permalink: perma))

And the translated to mysql query

SELECT `articles`.`id` AS t0_r0, `articles`.`title` AS t0_r1, 
       `articles`.`hypertitle` AS t0_r2, `articles`.`html` AS t0_r3,
       `articles`.`author` AS t0_r4, `articles`.`published` AS t0_r5,
       `articles`.`category_id` AS t0_r6, `articles`.`issue_id` AS t0_r7,
       `articles`.`date` AS t0_r8, `articles`.`created_at` AS t0_r9, 
       `articles`.`updated_at` AS t0_r10, `articles`.`photo_file_name` AS t0_r11,
       `articles`.`photo_content_type` AS t0_r12, `articles`.`photo_file_size` AS t0_r13,
       `articles`.`photo_updated_at` AS t0_r14, `categories`.`id` AS t1_r0,
       `categories`.`name` AS t1_r1, `categories`.`permalink` AS t1_r2,
       `categories`.`created_at` AS t1_r3, `categories`.`updated_at` AS t1_r4,
       `categories`.`issued` AS t1_r5, `categories`.`order_articles` AS t1_r6 
        FROM `articles` LEFT OUTER JOIN `categories` ON 
       `categories`.`id` = `articles`.`category_id` WHERE 
       `articles`.`issue_id` = 409 AND `categories`.`permalink` = 'Διεθνή' LIMIT 1

In the explation of this query I saw that uses wrong index

+----+-------------+------------+-------+---------------------------------------------------------------------------+-------------------------------+---------+-------+------+----------+-------------+
| id | select_type | table      | type  | possible_keys                                                             | key                           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+-------+---------------------------------------------------------------------------+-------------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | categories | const | PRIMARY,index_categories_on_permalink                                     | index_categories_on_permalink | 768     | const |    1 |   100.00 |             |
|  1 | SIMPLE      | articles   | ref   | index_articles_on_issue_id_and_category_id, index_articles_on_category_id | index_articles_on_category_id | 2       | const |   10 |   100.05 | Using where |
+----+-------------+------------+-------+---------------------------------------------------------------------------+-------------------------------+---------+-------+------+----------+-------------+

I have two indexes, category_id alone and issue_id - category_id.

In this query I'm searching with issue_id and category_id which is much faster when using the index_articles_on_issue_id_and_category_id than the index_articles_on_category_id.

How can I select the correct index with active record query?

ocodo
  • 29,401
  • 18
  • 105
  • 117
mike zaby
  • 478
  • 1
  • 3
  • 10
  • Mike for the specific example, you can use only one composite index to achieve the same results. If you have queries which use only the category_id and others which use category_id and issue_id (combined), the correct index would be `index_articles_on_category_id_and_issue_id`. With such an composite index you can exploit both query types. Take a look at this http://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index – alup Dec 16 '12 at 22:04

2 Answers2

40

You can facilitate arel like so to use an index:

 class Issue
   def self.use_index(index)
     # update: OP fixed my mistake
     from("#{self.table_name} USE INDEX(#{index})")
   end
 end

 # then
 Issue.use_index("bla").where(some_condition: true)
krichard
  • 3,699
  • 24
  • 34
  • 2
    Thanks, it works with a litle fix `from("#{self.table_name} USE INDEX(#{index})")` – mike zaby Dec 16 '12 at 18:40
  • Is there a way to achieve a similar effect without the risk of SQL injection? I tried the exact method implementation above but I'm getting Brakeman errors in my CI. – Richie Thomas Aug 31 '21 at 23:39
  • Also in form of a scope: `scope :use_index, ->(index) { from("#{table_name} USE INDEX(#{index})") }` – Florian Sep 03 '21 at 11:09
  • @RichieThomas A little dirty way to get away with brakeman errors on this `from(sanitize_sql_for_assignment("#{self.table_name} USE INDEX(#{index_name})"))` – Nikhil Mohadikar Mar 28 '22 at 10:30
  • Ran into an issue today using this, it's probably a good idea to put tick marks around the index name in case you've used a custom index name: ```from("#{self.table_name} USE INDEX(`#{index}`)")``` – Yardboy Nov 30 '22 at 23:22
5

Add use_index to ActiveRecord::Relation.

There was some discussion for multiple years about adding this to Rails core, however, it looks like the PR and branch got abandoned.

If you are aware of what database you're using and the limitations of this, you can easily add this to your own codebase so it can be used.

Very similar to @krichard's solution except more generalized for all models to use instead of just Issue.

config/initializers/active_record_relation.rb

class ActiveRecord::Relation
  # Allow passing index hints to MySQL in case the query planner gets confused.
  #
  # Example:
  #   Message.first.events.use_index( :index_events_on_eventable_type_and_eventable_id )
  #   #=> Event Load (0.5ms)  SELECT `events`.* FROM `events` USE INDEX (index_events_on_eventable_type_and_eventable_id) WHERE `events`.`eventable_id` = 123 AND `events`.`eventable_type` = 'Message'
  #
  # MySQL documentation:
  #    https://dev.mysql.com/doc/refman/5.7/en/index-hints.html
  #
  # See https://github.com/rails/rails/pull/30514
  #
  def use_index( index_name )
    self.from( "#{ self.quoted_table_name } USE INDEX ( #{ index_name } )" )
  end
end

This will allow you to use something like:

issue.articles.includes( :category ).use_index( :index_articles_on_issue_id_and_category_id )

And the resulting SQL will include:

FROM articles USE INDEX( index_articles_on_issue_id_and_category_id )
Joshua Pinter
  • 45,245
  • 23
  • 243
  • 245