DEV Community

n350071πŸ‡―πŸ‡΅
n350071πŸ‡―πŸ‡΅

Posted on

SQL tuning in Rails

πŸ€” 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 |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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" +
Enter fullscreen mode Exit fullscreen mode

πŸ¦„ 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

It's 531.7 times fasterπŸŽ‰


πŸ”— Parent Note

Top comments (0)