π€ Situation
- Something is slow.
- You dig the log files.
- Then, It's not the N+1 problem but maybe, It's specific query is slow problem.
π Solution
1. π§ Check what causes slow
We have explain method.
> Model.where(attribute: 'hoge').explain
Model Load (1364.2ms) SELECT `models`.* FROM `models` WHERE `models`.`attribute` = 'hoge'
=> EXPLAIN for: SELECT `models`.* FROM `models` WHERE `models`.`attibute` = 'hoge'
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | models | ALL | NULL | NULL | NULL | NULL | 1739502 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
Because of No index, MySQL has to search by Full scan. This causes it slow.
2. Treatment
Give it an index by db migration.
class AddIndexToModel < ActiveRecord::Migration[5.0]
def change
add_index :models, :attibute, name: :attibute_index_on_models
end
end
3. Check the Result
> Model.where(attibute: 'hoge').explain
Model Load (0.4ms) SELECT `models`.* FROM `models` WHERE `models`.`attibute` = 'hoge'
=> "EXPLAIN for: SELECT `models`.* FROM `models` WHERE `models`.`attibute` = 'hoge'\n" +
"+----+-------------+--------+------------+------+--------------------------+-------------------------+---------+-------+------+----------+-------+\n" +
"| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n" +
"+----+-------------+--------+------------+------+--------------------------+-------------------------+---------+-------+------+----------+-------+\n" +
"| 1 | SIMPLE | models | NULL | ref | attibute_index_on_models | attibute_index_on_models| 768 | const | 1 | 100.0 | NULL |\n" +
"+----+-------------+--------+------------+------+--------------------------+-------------------------+---------+-------+------+----------+-------+\n" +
π¦ Compare
After
> Model.where(attribue: 'hoge')
Model Load (2.6ms) SELECT `models`.* FROM `models` WHERE `models`.`attribue` = 'hoge' ORDER BY `models`.`id` ASC LIMIT 1
Before
> Model.where(attribue: 'hoge').first
Model Load (1382.4ms) SELECT `models`.* FROM `models` WHERE `models`.`attribue` = 'hoge' ORDER BY `models`.`id` ASC LIMIT 1
It's 531.7 times fasterπ
Top comments (0)