ItGo.me Focus on IT Recommend

Home > sql - Indexes for has_many :through

sql - Indexes for has_many :through

Suppose you have two models, User and City, joined by a third model CityPermission:

class CityPermission < ActiveRecord::Base
  belongs_to :city
  belongs_to :user
end

class City < ActiveRecord::Base
  has_many :city_permissions
  has_many :users, :through => :city_permissions
end

class User < ActiveRecord::Base
  has_many :city_permissions
  has_many :cities, :through => :city_permissions
end

Currently, I create the join table, and the index for the table, using the following migration code snippet:

create_table :city_permissions do |t|
      t.integer :user_id, :city_id
      t.other_fields ...
end

add_index(:city_permissions, :user_id)
add_index(:city_permissions, :city_id)

Are these the optimal indexes to create? Will these indexes allow quick access back and forth through the join table, as well as quick lookups within the table itself, or is there some other better way? To restate this a bit differently, will these indexes, given city and user are instance variables of class City and User, allow city.users, city.city_permissions, user.cities, and user.city_permissions to all perform equally well?

sql ruby-on-rails ruby database has-many
|
  this question
asked Oct 21 '08 at 22:37 jcnnghm 3,763 5 23 38

 | 

2 Answers
2

---Accepted---Accepted---Accepted---

Looks good to me.

The joins generated should just be on either the PK IDs of the entity tables, or on the FK IDs in the join table - which are both indexes.

Probably would be good to look at the generated ActiveRecord SQL and compare it against the indexes.

Depending on what database you're on you could then run that SQL through an Explain plan (or whatever tool exists, I'm thinking Oracle here)

To simplify your code, you could look at using has_and_belongs_to_many as well. That would let you get rid of the CityPermission object (unless you want to use that to store data in itself)


|
  this answer
answered Oct 21 '08 at 22:40 madlep 24.4k 7 33 51      I think it good to use polymorphic relatinship in this situation in case there's a need to extend the habtm relationship to other objects other than cities and users. –  JasonOng Oct 22 '08 at 1:10 1   he does mention he stores other info –  Tilendor Oct 22 '08 at 17:04      From my knowledge of rails 4, since rails automatically creates indexes for all primary keys, is it not necessary to add indexes on the foreign keys? –  Derrick Mar Aug 2 '15 at 5:08

 | 

Did you find this question interesting? Try our newsletter

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Subscribed! Success! Please click the link in the confirmation email to activate your subscription.

Here is the SQL that ActiveRecord generates for user.cities:

Recommend:database design - ruby on rails has_many through relationship

ng to find some help. So i've got Users & Lectures. Lectures are created by one user but then other users can then "join" the Lectures that have been created. Users have their own profile feed of the Lectures they have created & also have a

SELECT `cities`.* FROM `cities` INNER JOIN city_permissions ON (cities.id = city_permissions.city_id) WHERE (city_permissions.user_id = 1 )

EXPLAIN results below:

+----+-------------+------------------+--------+---------------------------------------------------------------------+-----------------------------------+---------+-------------------------------------------------+------+-------------+
| id | select_type | table            | type   | possible_keys                                                       | key                               | key_len | ref                                             | rows | Extra       |
+----+-------------+------------------+--------+---------------------------------------------------------------------+-----------------------------------+---------+-------------------------------------------------+------+-------------+
|  1 | SIMPLE      | city_permissions | ref    | index_city_permissions_on_user_id,index_city_permissions_on_city_id | index_city_permissions_on_user_id | 5       | const                                           |    1 | Using where |
|  1 | SIMPLE      | cities           | eq_ref | PRIMARY                                                             | PRIMARY                           | 4       | barhopolis_development.city_permissions.city_id |    1 |             |
+----+-------------+------------------+--------+---------------------------------------------------------------------+-----------------------------------+---------+-------------------------------------------------+------+-------------+

And here's the SQL that ActiveRecord generates for user.city_permissions:

SELECT * FROM `city_permissions` WHERE (`city_permissions`.user_id = 1)

With the EXPLAIN results for that query:

+----+-------------+------------------+------+-----------------------------------+-----------------------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys                     | key                               | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+-----------------------------------+-----------------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | city_permissions | ref  | index_city_permissions_on_user_id | index_city_permissions_on_user_id | 5       | const |    1 | Using where |
+----+-------------+------------------+------+-----------------------------------+-----------------------------------+---------+-------+------+-------------+

Looks like it is indeed working correctly. From the MySQL Manual:

eq_ref

One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE index.

ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.


|
  this answer
edited Oct 21 '08 at 22:58 answered Oct 21 '08 at 22:42 jcnnghm 3,763 5 23 38

 | 

Recommend:ruby on rails - Wrong sql generated by ActiveRecord for has_many :through relation with STI

, :through => :tagsendclass Second < ActiveRecord::Baseendclass Third < Second has_many :tags has_many :firsts, :through => :tagsendclass Tag < ActiveRecord::Base belongs_to :first belongs_to :thirdend In other words, we

oriUlr:http://stackoverflow.com/questions/223804/indexes-for-has-many-through

------splitte line----------------------------