Index FTW

gistfile1.md

I finally remember about index after running the rollback which was pretty fast - due to index on job_no.

Slowpoke

mysql> EXPLAIN SELECT * FROM comments, jobs WHERE target_type = 'Job' AND target_id = old_id;
+----+-------------+----------+------+---------------+------+---------+------+-------+------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra                                          |
+----+-------------+----------+------+---------------+------+---------+------+-------+------------------------------------------------+
|  1 | SIMPLE      | jobs     | ALL  | NULL          | NULL | NULL    | NULL | 13498 |                                                |
|  1 | SIMPLE      | comments | ALL  | target        | NULL | NULL    | NULL | 58356 | Range checked for each record (index map: 0x2) |
+----+-------------+----------+------+---------------+------+---------+------+-------+------------------------------------------------+
2 rows in set (0.00 sec)

After adding an index to old_id column

==  JobCommentsToUseJobNo: migrating ==========================================
-- execute("UPDATE comments, jobs SET comments.target_id = jobs.job_no WHERE target_type = 'Job' AND target_id = old_id")
   -> 1.8815s
==  JobCommentsToUseJobNo: migrated (1.8816s) =================================

mysql> EXPLAIN SELECT * FROM comments, jobs WHERE target_type = 'Job' AND target_id = old_id;
+----+-------------+----------+------+---------------+--------+---------+---------------------------------------+-------+-------------+
| id | select_type | table    | type | possible_keys | key    | key_len | ref                                   | rows  | Extra       |
+----+-------------+----------+------+---------------+--------+---------+---------------------------------------+-------+-------------+
|  1 | SIMPLE      | comments | ALL  | target        | NULL   | NULL    | NULL                                  | 40076 | Using where |
|  1 | SIMPLE      | jobs     | ref  | old_id        | old_id | 5       | beqik2_development.comments.target_id |    65 | Using where |
+----+-------------+----------+------+---------------+--------+---------+---------------------------------------+-------+-------------+