本文介绍了在 Rails 中使用特定的 mysql 索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有这个 ActiveRecord 查询
I have this ActiveRecord query
issue = Issue.find(id)
issue.articles.includes(:category).merge(Category.where(permalink: perma))
翻译成mysql查询
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 |
+----+-------------+------------+-------+---------------------------------------------------------------------------+-------------------------------+---------+-------+------+----------+-------------+
我有两个索引，category_id 和 issue_id - category_id.
I have two indexes, category_id alone and issue_id - category_id.
在此查询中，我使用 issue_id 和 category_id 进行搜索，使用 index_articles_on_issue_id_and_category_id 比 index_articles_on_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?
推荐答案
你可以像这样方便使用索引:
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)
这篇关于在 Rails 中使用特定的 mysql 索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！