It would appear that quite often we join versioncontrol_item_revisions against versioncontrol_operations to get full commit info. This join is usually done ON vc_op_id, but there is no index on versioncontrol_item_revisions.vc_op_id. We should add one, as without it we tend to have a full table scan on item_revisions. Example:
Before
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | versioncontrol_repositories | index | PRIMARY | PRIMARY | 4 | NULL | 9016 | Using index |
| 1 | SIMPLE | versioncontrol_item_revisions | ref | repo_id_path | repo_id_path | 4 | git_dev.versioncontrol_repositories.repo_id | 48 | Using where |
| 1 | SIMPLE | versioncontrol_operations | eq_ref | PRIMARY | PRIMARY | 4 | git_dev.versioncontrol_item_revisions.vc_op_id | 1 | Using index |
After
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | versioncontrol_item_revisions | range | repo_id_path,test | test | 4 | NULL | 14 | Using where |
| 1 | SIMPLE | versioncontrol_operations | eq_ref | PRIMARY | PRIMARY | 4 | git_dev.versioncontrol_item_revisions.vc_op_id | 1 | Using index |
| 1 | SIMPLE | versioncontrol_repositories | eq_ref | PRIMARY | PRIMARY | 4 | git_dev.versioncontrol_item_revisions.repo_id | 1 | |
Comments
Comment #1
halstead commentedHere is a quick patch to add the index.
Comment #2
dww#1 had some trivial whitespace bugs. Fixed here. Tested locally and works fine. Committed to HEAD: http://drupal.org/cvs?commit=501772
Thanks!
-Derek
Comment #3
halstead commentedKomodo always snips off the trailing new line. Thanks for catching that.
Comment #4
dww@halstead: That wasn't the only one. There was also a missing space before $ret. I also reformatted the PHPDoc block to follow our coding standards...
Cheers,
-Derek
Comment #5
halstead commentedHere is dww's cleaned up version that was actually committed.
Comment #6
halstead commentedI didn't mean to change the assignment just provide the patch.