Rails v. 5.2.4
ActiveRecord v5.2.4.3
I have a Rails app with a MySQL database, and my app has a Skill model and a SkillAdjacency model. The SkillAdjacency model has the following attributes:
requested_skill_id, table_name: 'Skill'
adjacent_skill_id, table_name: 'Skill'
score, integer
SkillAdjacencies are used to determine how "similar" two instances of Skill are to each other.
One of the app's constraints is that you can't create more than one instance of SkillAdjacency for each combination of requested_skill and adjacent_skill, and I plan to enforce this both with ActiveModel validations and with a composite index which employs a uniqueness constraint. So far I have the following:
add_index :skill_adjacencies, [:requested_skill_id, :adjacent_skill_id], unique: true, name: 'index_adjacencies_on_requested_then_adjacent', using: :btree
However, I know that the order in which the composite columns are declared is important, so I'm considering adding this 2nd composite index to account for the other possible order:
add_index :skill_adjacencies, [:adjacent_skill_id, :requested_skill_id], unique: true, name: 'index_adjacencies_on_adjacent_then_requested', using: :btree
But because writing to an index isn't free, I only want to add the 2nd index if it will actually result in a performance benefit. The problem is, whether or not this 2nd index will be beneficial depends on whether ActiveRecord will start with adjacent_skill_id vs. requested_skill_id when searching for a composite index to search.
How can I determine what order ActiveRecord uses? Does it just use the same order that's specified in the query? For example, if I query SkillAdjacency.where(requested_skill: Skill.last, adjacent_skill: Skill.first), will it always search for a composite index composed of requested_skill 1st and adjacent_skill 2nd? If that's the case, should I cover all my bases by creating that additional composite index?
Alternately, is there some under-the-hood magic which determines if the relevant composite index exists regardless of the order provided in the query?
EDIT:
I ran EXPLAIN and saw the following:
irb(main):013:0> SkillAdjacency.where(requested_skill_id: 1, adjacent_skill_id: 200).explain
SkillAdjacency Load (0.3ms) SELECT `skill_adjacencies`.* FROM `skill_adjacencies` WHERE `skill_adjacencies`.`requested_skill_id` = 1 AND `skill_adjacencies`.`adjacent_skill_id` = 200
=> EXPLAIN for: SELECT `skill_adjacencies`.* FROM `skill_adjacencies` WHERE `skill_adjacencies`.`requested_skill_id` = 1 AND `skill_adjacencies`.`adjacent_skill_id` = 200
+----+-------------+-------------------+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | skill_adjacencies | NULL | const | index_adjacencies_on_requested_then_adjacent,index_adjacencies_on_adjacent_then_requested,index_skill_adjacencies_on_requested_skill_id,index_skill_adjacencies_on_adjacent_skill_id | index_adjacencies_on_requested_then_adjacent | 10 | const,const | 1 | 100.0 | NULL |
+----+-------------+-------------------+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+-------------+------+----------+-------+
1 row in set (0.00 sec)
irb(main):014:0> SkillAdjacency.where(adjacent_skill_id: 200, requested_skill: 1).explain
SkillAdjacency Load (0.3ms) SELECT `skill_adjacencies`.* FROM `skill_adjacencies` WHERE `skill_adjacencies`.`adjacent_skill_id` = 200 AND `skill_adjacencies`.`requested_skill_id` = 1
=> EXPLAIN for: SELECT `skill_adjacencies`.* FROM `skill_adjacencies` WHERE `skill_adjacencies`.`adjacent_skill_id` = 200 AND `skill_adjacencies`.`requested_skill_id` = 1
+----+-------------+-------------------+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | skill_adjacencies | NULL | const | index_adjacencies_on_requested_then_adjacent,index_adjacencies_on_adjacent_then_requested,index_skill_adjacencies_on_requested_skill_id,index_skill_adjacencies_on_adjacent_skill_id | index_adjacencies_on_requested_then_adjacent | 10 | const,const | 1 | 100.0 | NULL |
+----+-------------+-------------------+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+-------------+------+----------+-------+
1 row in set (0.00 sec)
In both cases, I see that the value in the key column is index_adjacencies_on_requested_then_adjacent, despite each query passing in a different order for the query params. Can I assume this means the order of those params doesn't matter?