yohoushi/yohoushi — pull request #62 by sonots (2014-12-17 07:58:26+00:00)
Title: add index index_taggings_on_taggable_id(taggable_id ...) for acts-as-taggable-on

Fix Problem4 of #61.

I ran `mysql> show full processlist` when CPU usage of mysqld is high.

```
[01:43:08 root@localhost/(none) :3] show full processlist;
| Id | User | Host | db | Command | Time | State | Info
| 875146786 | root | localhost | yohoushi | Query | 0 | Sending data | SELECT `tags`.* FROM `tags` INNER JOIN `taggings` ON `tags`.`id` = `taggings`.`tag_id` WHERE `taggings`.`taggable_id` = 18687 AND `taggings`.`taggable_type` = 'Node' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) |
```

Explain the query. Number of rows is pretty large. It seems that index is not working well.

```
[02:46:09 root@localhost/yohoushi :6] explain SELECT `tags`.* FROM `tags` INNER JOIN `taggings` ON `tags`.`id` = `taggings`.`tag_id` WHERE `taggings`.`taggable_id` = 18687 AND `taggings`.`taggable_type` = 'Node' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) ;
+----+-------------+----------+--------+---------------+--------------+---------+--------------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+---------------+--------------+---------+--------------------------+--------+--------------------------+
| 1 | SIMPLE | taggings | index | taggings_idx | taggings_idx | 1938 | NULL | 887571 | Using where; Using index |
| 1 | SIMPLE | tags | eq_ref | PRIMARY | PRIMARY | 4 | yohoushi.taggings.tag_id | 1 | |
+----+-------------+----------+--------+---------------+--------------+---------+--------------------------+--------+--------------------------+
2 rows in set (0.00 sec)

[02:50:16 root@localhost/yohoushi :5] show create table taggings;
...
PRIMARY KEY (`id`),
UNIQUE KEY `taggings_idx` (`tag_id`,`taggable_id`,`taggable_type`,`context`,`tagger_id`,`tagger_type`),
...
```

Created another index not including `tag_id`. Now, mysql processes WHERE first, then JOIN, which is much efficient in this case.

```
[02:50:08 root@localhost/yohoushi :4] alter table taggings add index index_taggings_on_taggable_id(taggable_id, taggable_type, context, tagger_id, tagger_type);

[02:50:08 root@localhost/yohoushi :4] explain SELECT `tags`.* FROM `tags` INNER JOIN `taggings` ON `tags`.`id` = `taggings`.`tag_id` WHERE `taggings`.`taggable_id` = 18687 AND `taggings`.`taggable_type` = 'Node' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL);
+----+-------------+----------+--------+--------------------------------------------+-------------------------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+--------------------------------------------+-------------------------------+---------+--------------------------+------+-------------+
| 1 | SIMPLE | taggings | ref | taggings_idx,index_taggings_on_taggable_id | index_taggings_on_taggable_id | 1165 | const,const,const,const | 5 | Using where |
| 1 | SIMPLE | tags | eq_ref | PRIMARY | PRIMARY | 4 | yohoushi.taggings.tag_id | 1 | |
+----+-------------+----------+--------+--------------------------------------------+-------------------------------+---------+--------------------------+------+-------------+
2 rows in set (0.00 sec)
```