Posted by Bart ten Brinke Wed, 09 Apr 2008 19:51:20 GMT
When you are developing your application, you should allways look for the following line in your development console.
Processing EmployeesController#index[GET]
Employee Load (0.055003)
SELECT * FROM `people` WHERE `people`.`type` = 'Employee'
select_type | key_len | type | Extra |
--------------------------------------------- =>
SIMPLE | | ALL | Using where |
| id | possible_keys | rows | table | ref | key
--------------------------------------------------
| 1 | | 6965 | people | |
The type ALL means that you are preforming a full table scan in a query. This is usually not a problem when you are in development mode, but what is you have millions of people in your database?
Usually it is pretty straight forward to find out where you were calling this from, as you will probably remember what the request was you did. If you are having a hard time, install the query_trace plugin. This gives the following result:
vendor/plugins/query_analyzer/lib/query_analyzer.rb:38:in `select'
app/controllers/employees_controller.rb:71:in `find'
app/controllers/employees_controller.rb:71:in `index'
vendor/plugins/browser-prof/lib/browser-prof.rb:32:in `process'
Looking at line 71 of the employees controller is a good idea here as you might be doing something stupid. As line 71 just reads: @employees = Employee.find(:all)) we have to turn to your database.
mysql -u root --database myapp_development
mysql> EXPLAIN SELECT * FROM `people` WHERE `people`.`type` = 'Employee';
+----+-------------+--------+------+------------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+------------------+ =>
| 1 | SIMPLE | people | ALL | |
+----+-------------+--------+------+------------------+
+------+---------+------+------+-------------+
| key | key_len | ref | rows | Extra |
+------+---------+------+------+-------------+
| NULL | NULL | NULL | 6873 | Using where |
+---------+------+------+------+-------------+
1 row in set (0.00 sec)
As you can see we are not hitting any indexes. Lets try adding an index.
mysql> create index people_type_test on people (type);
Query OK, 6715 rows affected (1.38 sec)
Records: 6715 Duplicates: 0 Warnings: 0
Now we run the explain again:
mysql> EXPLAIN SELECT * FROM `people` WHERE `people`.`type` = 'Employee' ;
+----+-------------+--------+------+------------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+------------------+ =>
| 1 | SIMPLE | people |range | people_type_test |
+----+-------------+--------+------+------------------+
+------------------+---------+-------+------+-------------+
| key | key_len | ref | rows | Extra |
+------------------+---------+-------+------+-------------+
| people_type_test | 768 | const | 2496 | Using where |
+------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
Thats more like it, now we need to add this to our app trough a migration.
class CreatePeopleIndices < ActiveRecord::Migration
def self.up
add_index :people, :type
end
def self.down
remove_index :people, :type
end
end
After a db:migrate and a restart of the server, we now see the following in the development console:
Employee Load (0.027666)
SELECT * FROM `people` WHERE `people`.`type` = 'Employee'
Analyzing Employee Load
select_type | key_len | type | Extra |
--------------------------------------------- =>
SIMPLE | 768 | ref | Using where |
| id | possible_keys | rows | table | ref | key
----------------------------------------------------------------------
| 1 | index_people_on_type | 2496 | people | const | people_type_test
Success! Also note that the load on the database has been cut in half.
