CODEWITHSUNDEEP
mysqlruby-on-railsrubyindexinghas-many-through
user984621
user984621

Reputation: 48453

How to properly add indexes on this through-many association?

I have a following scheme of models:

hotel (250,000 records)

class Hotel < ActiveRecord::Base
  has_many :hotel_services, dependent: :destroy
  has_many :services, through: :hotel_services
end

service (60 records)

class Service < ActiveRecord::Base
  has_many :hotel_services
  has_many :hotels, through: :hotel_services
end

hotel_service (1,200,000 records)

class HotelService < ActiveRecord::Base
  belongs_to :hotel
  belongs_to :service
end

I am facing the n+1 problem. I am running a query like this:

@hotels = Hotel.includes(:services).where(...)

This query is executed pretty fast (1-2 seconds), but because of the has-many relationship and 1,200,000 million records on the table hotel_services, this part takes between 30-45 seconds (depends on the where part).

I was thinking about using indexes to speed up executing queries, but which one should I use in this scheme?

Thank you in advance, guys.

EDIT: Adding indexes on the hotel_services table:

| Table            | Non_unique | Key_name                                            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+-----------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| hotel_services   |          0 | PRIMARY                                             |            1 | id          | A         |     1044995 |     NULL | NULL   |      | BTREE      |         |               |
| hotel_services   |          1 | index_hotel_services_on_hotel_id_and_service_id |            1 | hotel_id  | A         |      522497 |     NULL | NULL   | YES  | BTREE      |         |               |
| hotel_services   |          1 | index_hotel_services_on_hotel_id_and_service_id |            2 | service_id  | A         |     1044995 |     NULL | NULL   | YES  | BTREE      |         |               |

and generated EXPLAIN command:

+----+-------------+------------------+-------+-----------------------------------------------------+-----------------------------------------------------+---------+------+-------+-----------------------+
| id | select_type | table            | type  | possible_keys                                       | key                                                 | key_len | ref  | rows  | Extra                 |
+----+-------------+------------------+-------+-----------------------------------------------------+-----------------------------------------------------+---------+------+-------+-----------------------+
|  1 | SIMPLE      | hotel_services   | range | index_hotel_services_on_hotel_id_and_service_id | index_hotel_services_on_hotel_id_and_service_id | 5       | NULL | 10254 | Using index condition |
+----+-------------+------------------+-------+-----------------------------------------------------+-----------------------------------------------------+---------+------+-------+-----------------------+
1 row in set (0.36 sec)

Upvotes: 1

Views: 70

Answers (3)

Fer
Fer

Reputation: 3347

Index theory is quite big, and you should read further somewhere else.

Anyway, for your particular problem, the big boost will be when you add an index on both fields in your hotel_services

In your migration file:

add_index :hotel_services, [:hotel_id, :service_id]

Sometimes the generated index name is too long and mysql complains about it (this should not be the case, but just to cover some edge cases). In this case I usually name the index:

add_index :hotel_services, [:hotel_id, :service_id], name: :on_foreign_keys

And a completely opinionated comment: 1-2 seconds for a query to run is a lot of time.

For specific query issues you can use the explain command.

mysql> explain select * from users;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL |  129 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

As a hint, the lower the rows value, the better. Adding indices to the DB will usually reduce the number.

Candidates to be indexed:

  • Fields involved in joins clauses
  • Fields involved in where claues

Try to avoid type=ALL (full access) in big tables

Indexing elements located in the select clause is not helpful

Upvotes: 1

Hetal Khunti
Hetal Khunti

Reputation: 807

Rails applications will get significant performance boost if the sql queries sent to the database are optimized. Database must not be hit with requests unless there is an absolute necessity.

Two Basic steps for Optimization:

1. Add database indexes on all foreign keys

add_index :tasks, :project_id

2. Use Eager loading to avoid n+1 query problems, in sensible places.

Project.find(12).includes(:tasks, :notes)

Upvotes: 0

user1646075
user1646075

Reputation:

The usual keys on the ID of the two main tables will be sufficient for those tables. I'd be inclined to add a two-field index encompassing hotel_id and service_id on the link table, rather than one each on each field.

It'll be quite easy to test, so play around a bit. Add them manually in mysql (or whatever tool for your particular db brand) before making the final migration

Upvotes: 0

Related Questions