ruby-on-rails - ajax请求部分中的非空查询非常慢

Question

我有这个简单的范围

scope :has_video_link,
        -> { where.not(video_link: nil) }

我在控制器中像这样使用它

    @videos = Message          .featured
                               .includes(:user, :company, :forum, :topic)
                               .published
                               .unremoved
                               .approved
                               .has_video_link
                               .order(created_at: :desc)
                               .paginate(
                                 page:     page,
                                 per_page: limit)

我这样称呼

  before_action only: :index

  skip_before_action :verify_authenticity_token

  def index
    self.page_title = 'Home'
    fetch_videos
  end

这工作得很好，我的页面在一秒钟内加载，没有问题。我的问题是我有一个调用它的 ajax 请求

  def videos
    fetch_videos

    respond_to do |format|
      format.js
    end
  end

在开发中，这很好用，但在产品中，这需要 60 多秒......第一张图片来自第一次调用（4 毫秒）第二张图片是 ajax 调用 69000 毫秒......

有任何想法吗？我该如何解决？？谢谢！！

编辑：这是解释

EXPLAIN for: SELECT  `messages`.* FROM `messages` WHERE `messages`.`is_featured` = 1 AND (messages.created_at<='2021-06-04 12:36:22.241601') AND `messages`.`is_removed` = 0 AND `messages`.`is_approved` = 1 AND (`messages`.`video_link` IS NOT NULL) ORDER BY `messages`.`created_at` DESC LIMIT 5 OFFSET 0
+----+-------------+----------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+---------+------+------+----------+------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | key                                         | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+----------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | messages | NULL       | range | index_messages_on_is_removed_and_user_id_and_created_at,index_messages_on_is_removed_and_message_type_and_created_at,index_messages_on_is_removed_company_id_type_created_at,index_messages_on_is_removed_company_id_created_at_id_user_id,index_messages_on_is_removed_and_created_at,index_messages_on_is_removed_company_id_created_at_rating_count,index_messages_on_is_removed_company_id_user_role_created_at,index_messages_on_is_removed_and_company_id_and_rating_total,index_messages_on_is_removed_and_forum_id_and_created_at,index_messages_on_is_approved,index_messages_on_is_removed_and_is_approved | index_messages_on_is_removed_and_created_at | 7       | NULL | 3470 |      9.0 | Using index condition; Using where |
+----+-------------+----------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+---------+------+------+----------+------------------------------------+
1 row in set (0.00 sec)

EXPLAIN for: SELECT `users`.* FROM `users` WHERE `users`.`id` = 594568
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |    100.0 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

EXPLAIN for: SELECT `companies`.* FROM `companies` WHERE `companies`.`id` IN (564013, 562440)
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | companies | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |    100.0 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)

EXPLAIN for: SELECT `forums`.* FROM `forums` WHERE `forums`.`id` IN (12224, 7759)
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | forums | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |    100.0 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

EXPLAIN for: SELECT `topics`.* FROM `topics` WHERE `topics`.`id` IN (684474, 684473, 684472, 684470, 684467)
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | topics | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    5 |    100.0 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

标签： ruby-on-railsruby

Answer 1

解决了这个问题。我所要做的就是简单地建立索引video_links，然后就解决了我的问题！